BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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

 

 

 

 

 

 

10 REPLIES 10
Shmuel
Garnet | Level 18

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.

ballardw
Super User

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.

 

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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.

Shmuel
Garnet | Level 18

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)  
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

ok, will do

mkeintz
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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

mkeintz
PROC Star

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

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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

Shmuel
Garnet | Level 18
You mixed terms.
8Feb2017:00:00:00 is a datetime not a date.
8feb2017 is a date.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 976 views
  • 1 like
  • 4 in conversation