DATA Step, Macro, Functions and more

PROC SQL

Reply
Frequent Contributor
Frequent Contributor
Posts: 98

PROC SQL

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

 

 

 

 

 

 

Trusted Advisor
Posts: 1,554

Re: PROC SQL

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.

Super User
Posts: 11,343

Re: PROC SQL

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.

 

 

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC SQL

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.

Trusted Advisor
Posts: 1,554

Re: PROC SQL

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)  
Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC SQL

ok, will do

Trusted Advisor
Posts: 1,018

Re: PROC SQL

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

 

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC SQL

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

Trusted Advisor
Posts: 1,018

Re: PROC SQL

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

 

 

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC SQL

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

Trusted Advisor
Posts: 1,554

Re: PROC SQL

You mixed terms.
8Feb2017:00:00:00 is a datetime not a date.
8feb2017 is a date.
Ask a Question
Discussion stats
  • 10 replies
  • 236 views
  • 1 like
  • 4 in conversation