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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.