topic Re: Proc sql problem using where statement in SAS Programming

Proc sql problem using where statement

Simo — Wed, 19 Aug 2009 14:03:43 GMT

Hi,
I need to match cases from 2 datasets in a way that they match in region, ID, diagnose code and date.

region, ID and diagnose are exact matches but date can be a match from 3 different date variables in database 2.

Here's the syntax

PROC SQL;
CREATE TABLE d1d2
AS
SELECT*
FROM d1,d2
WHERE d1.region=d2.region2 and d1.ID=d2.ID2 and d1.diagnose=d2.diagnose and
d1.date=d2.date1 OR
d1.date=d2.date2 OR
d1.date=d2.date3 OR
;
QUIT;

Works fine until the OR so what how to get it to work?

S

Re: Proc sql problem using where statement

Flip — Wed, 19 Aug 2009 14:29:52 GMT

At first glance, you have an extra 'OR'. I would also throw in some parentheses to control presedence.

WHERE d1.region=d2.region2 and d1.ID=d2.ID2 and d1.diagnose=d2.diagnose and
(d1.date=d2.date1 OR
d1.date=d2.date2 OR
d1.date=d2.date3 )
;

Re: Proc sql problem using where statement

Simo — Thu, 20 Aug 2009 04:44:26 GMT

Thanks a lot, seems to work now. Needed the brackets.

S

Re: Proc sql problem using where statement

Peter_C — Fri, 21 Aug 2009 12:29:16 GMT

the brackets were not enough ~~ you also needed to lose the last (trailing) OR.

technically speaking, OR is an INFIX operator
It needs something after the OR as well as before it.

PeterC