PROC SQL

[JHE]

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

[Shmuel]

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]

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]

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]

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]

ok, will do

[mkeintz]

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

[JHE]

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

[mkeintz]

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

[JHE]

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

[Shmuel]

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