topic Re: PROC SQL in SAS Programming

PROC SQL

JHE — Thu, 10 Aug 2017 16:09:52 GMT

My code,

in a Proc SQL

1. if I am using :

WHERE (t1.CLAIM_ADMIT_DATE BETWEEN '8Feb2017:0:0:0'dt AND '8Aug2017:0:0:0'dt)

it will have data return. with 'other' condition.

2. If I am using:

(t1.CLAIM_ADMIT_DATE >= TODAY()-183 AND t1.CLAIM_ADMIT_DATE <=TODAY()-7) only, will have data return.

3. but If I am using:

(t1.CLAIM_ADMIT_DATE >= TODAY()-183 AND t1.CLAIM_ADMIT_DATE <=TODAY()-7) with same 'other' condition,

wil no data return.

why

Re: PROC SQL

Shmuel — Thu, 10 Aug 2017 16:48:41 GMT

Are you sure that CLAIM_ADMIT_DATE is a datetime variable and not a date variable?

If it is a date variable use date literal like: '08feb2007'd.

Re: PROC SQL

ballardw — Thu, 10 Aug 2017 16:52:21 GMT

What is an "other" condition? values please of Claim_admit_date.

Though I agree with @Shmuel that confirmation of actual data value type is important.

Re: PROC SQL

JHE — Thu, 10 Aug 2017 17:16:55 GMT

Yes, it is 08FEB2017:00:00:00

Other condition as below:

((substr(t1.bill_type,1,2) not in('11','12','21','22','26')

and substr(t1.bill_type,2,2) not in('11','12','21','22','26')))

If I am using :

WHERE (t1.CLAIM_ADMIT_DATE BETWEEN '8Feb2017:0:0:0'dt AND '8Aug2017:0:0:0'dt)

AND (substr(t1.bill_type,1,2) not in('11','12','21','22','26')

and substr(t1.bill_type,2,2) not in('11','12','21','22','26'));

I am able to have reult.

but If I am using:

WHERE (t1.CLAIM_ADMIT_DATE >= TODAY()-183 AND t1.CLAIM_ADMIT_DATE <=TODAY()-7))

AND (substr(t1.bill_type,1,2) not in('11','12','21','22','26')

and substr(t1.bill_type,2,2) not in('11','12','21','22','26'));

Will no result at all.

also if I am only using:

WHERE (t1.CLAIM_ADMIT_DATE >= TODAY()-183 AND t1.CLAIM_ADMIT_DATE <=TODAY()-7)

I will get the result.

make me cry.

Re: PROC SQL

Shmuel — Thu, 10 Aug 2017 17:52:03 GMT

The expression TODAY() - 183 is a date, not a datetime !

Change statement from

(t1.CLAIM_ADMIT_DATE >= TODAY()-183 AND t1.CLAIM_ADMIT_DATE <=TODAY()-7)  only, will have data return.

into:

(datepart(t1.CLAIM_ADMIT_DATE)  >= TODAY()-183 AND datepart(t1.CLAIM_ADMIT_DATE) <=TODAY()-7)

Re: PROC SQL

mkeintz — Thu, 10 Aug 2017 17:52:26 GMT

You problem is that

today()-183 is not the same as '8Feb2017:0:0:0'dt

That's because toda()-183 built with a DATE value (number of days after 01jan1960 minus 183 days), while '8Feb2017:0:0:0'dt is a DATETIME value (number of SECONDS after 01jan1960:00:00:00).

The varname CLAIM_ADMIT_DATE suggests you need date values, so I would use '8Feb2017'd instead of '8Feb2017:0:0:0'dt as the date literal.

But your results came when you used the datetime literal, so maybe "CLAIM_ADMIT_DATE is actually a dateimte value. In that case, use

DATEPART(claim_admit_date) between today()-183 and today()-7

Re: PROC SQL

JHE — Thu, 10 Aug 2017 18:07:22 GMT

Oh, I see, what I should do ,,,, the correct way ?

Re: PROC SQL

mkeintz — Thu, 10 Aug 2017 18:14:30 GMT

What is CLAIM_ADMIT_DATE - a date value or a datetime value?

Re: PROC SQL

JHE — Thu, 10 Aug 2017 18:20:15 GMT

A date value , = 8Feb2017:00:00:00

Re: PROC SQL

JHE — Thu, 10 Aug 2017 18:21:26 GMT

ok, will do

Re: PROC SQL

Shmuel — Thu, 10 Aug 2017 18:22:05 GMT

You mixed terms.
8Feb2017:00:00:00 is a datetime not a date.
8feb2017 is a date.