Date: Fri, 11 May 2012 08:33:44 -0700
Reply-To: Irin later <irinfigvam@yahoo.com>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Irin later <irinfigvam@YAHOO.COM>
Subject: Re: The datepart function requires 2 argument(s)!
In-Reply-To: <BLU152-W64874822C8FDE5F79BB842DE160@phx.gbl>
Content-Type: text/plain; charset=utf-8
Thank you so much to Toby and all who responded. I decided to implement it on SQL Server instead of involing SAS for these steps
However , when I tried to implement the following :
select *
FROM irHospDisch_ a
inner join irHospt_Visits12 a2 on a1.claimid=a2.claimid
where a2.ServiceDateFrom >= datepart(a.AdmitDate) and datepart(a.AdmitDate) is not null
and a2.ServiceDateFrom between '01OCT2010' and '30SEP2011'
I got Error stated
Msg 174, Level 15, State 1, Line 56
The datepart function requires 2 argument(s).
Fileds look like the following (I do not know how to see similar as Proc contents in SAS):
Admitdate 2010-11-16 00:00:00
ServiceDateFrom 2010-10-01 00:00:00.000
I tried datepart function on the left site of the statement, on the right one, on both....
What I am doing wrong?
Thank you so much in advance,
Irin
From: toby dunn <tobydunn@hotmail.com>
To: irinfigvam@yahoo.com; sas-l@listserv.uga.edu
Sent: Thursday, May 10, 2012 3:53 PM
Subject: RE: Cartesian because of incompatibility of SQL Server and SAS dataset?
I suspect your problem isnt a cartesian product but rather th enumber of inner joins you are doing and the fact that you doing this in SAS which means SAS has to pull the entire table over from the database and then perform the joins. Do I'd look at your network to see if there is a bandwith issue or try to shove as much as you can on the Server side and then do the joins.... Now you can get a temp file which is a cartesian product but you never see those as they are temp files that SQL creates behind the scenes.
Toby Dunn
If you get thrown from a horse, you have to get up and get back on, unless you landed on a cactus; then you have to roll around and scream in pain.
“Any idiot can face a crisis—it’s day to day living that wears you out”
~ Anton Chekhov
> Date: Thu, 10 May 2012 12:10:17 -0700
> From: irinfigvam@YAHOO.COM
> Subject: Cartesian because of incompatibility of SQL Server and SAS dataset?
> To: SAS-L@LISTSERV.UGA.EDU
>
> It looks like somehow I involved myself into Cartesian output. In my PROC SQL below HospDisch is SAS dataset while all other tables come from SQL SERVER 2005 (DW).
> Is it possible that data from SAS data set just "do not see" data from SQL server? Actually I tried substr() function to join character fields but it looks like it still is running forever and will give me astronomic # of records at the end...
>
> Could it be a compatability problem? If so what can I do?What do you think? Can you give me a hand as I am in desperate now:(
> Thank you in advance,
> Irin
>
>
> Proc SQL;
> Create table Hospt_Visits as
> select
> a.Memberid
> ,cl.claimid
> ,cl.ServiceDateFrom
> ,a.admitdt
> ,a.LOSp
> ,a.HospDischDate
> ,a.AGE
> ,a.HOSPID
> ,a.DRG
> ,a.PR1
> ,a.PR2
> ,a.PR3
> ,a.PR4
> ,a.PR5
> ,a.PR6
> ,a.PR7
> ,a.PR8
> ,a.PROGRAM
> ,a.CLAIMID as HospClaim
> ,a.paidamount
> ,a.PCPSiteid
> ,a.PCPSitePId
> ,a.PCPSiteName
> ,a.MemberName
> ,a.eventid1
> ,a.PayToProviderid
> ,a.PayToProviderName
> ,ch.claimstatus
> FROM MYLIBREF.ClaimLine cl
> INNER JOIN MYLIBREF.CoeEventDimMap CEDM ON (CEDM.ClaimID = CL.ClaimID and CEDM.LineNumber = cl.LineNumber)
> INNER JOIN MYLIBREF.Event_DIM ED ON ED.Event_PK = CEDM.Event_PK
> inner join MYLIBREF.Claimheader ch ON CH.ClaimID = CL.ClaimID
> INNER JOIN HospDisch a on substr(a.memberid,1,10)=substr(ch.memberid,1,10)
> where substr(ed.level3,1,9)='Physician'
> and (
> (year(datepart(cl.ServiceDateFrom))=2010 and month(datepart(cl.ServiceDateFrom))in (10,11,12))
> or
> (year(datepart(cl.ServiceDateFrom))=2011 and month(datepart(cl.ServiceDateFrom))in (1,2,3,4,5,6,7,8,9))
> );
> quit;
| 
