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
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.
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.
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.
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)
ok, will do
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
to
Oh, I see, what I should do ,,,, the correct way ?
What is CLAIM_ADMIT_DATE - a date value or a datetime value?
A date value , = 8Feb2017:00:00:00
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.