Case Statement logic

[itshere]

Hi - 

using proc sql;

Im trying to make a query that says, if the call happened within 7 days after the case creation then add a 1, otherwise 0 and if the call happened within 31 days after the case creation add a 1, otherwise 0. 

the date is in the following format 01MAY2016 11:10:00 (datetime20) both case and call. 

this is my formula currently but it didnt work correct. 

proc sql;
create table test.rock as
select A.*,
(case when datepart( A.call)>datepart(a.case)+7 then 1 else 0 end) as call_in_7days,
(case when datepart( A.call)>datepart(a.case )+31 then 1 else 0 end) as call_in_31days
from test.paper A

;

but the results were incorrect. please advise if my logic is incorrect. 

[Reeza]

Look at cases where it didn't work. 

What were the values of Call and Case in those cases? Also, should your comparison be > or >=?

Your logic is incorrect though. 

You want the Call to be between Case and Case+7 , your checking if it's more than 7 days, which is a Call more than 7 days after a Case Creation. 

[ChrisNZ]

I find

proc sql;
create table test.rock as
select A.*,
(case when datepart( A.call)-datepart(a.case) > 7 then 1 else 0 end) as call_in_7days,
(case when datepart( A.call)-datepart(a.case ) > 31 then 1 else 0 end) as call_in_31days
from test.paper A

;

easier to read. And then you see your mistake. You probably mean

create table test.rock as
select A.*,
(case when datepart( A.call) - datepart(a.case) <= 7 then 1 else 0 end) as call_in_7days,
(case when datepart( A.call) - datepart(a.case ) <= 31 then 1 else 0 end) as call_in_31days
from test.paper A

which can also be written

select A.*,
( datepart( A.call) - datepart(a.case) <= 7 ) as call_in_7days,
( datepart( A.call) - datepart(a.case ) <= 31 ) as call_in_31days
from test.paper A

[PGStats]

Simplify your code and make it independent of internal date and time representation. Use SAS date and time interval functions (intnx and intck). The "DTDAY" interval represents a day for datetime values. 

proc sql;
create table test.rock as
select 
	*,
	intck("DTDAY", case, call) <= 7  as call_in_7days,
	intck("DTDAY", case, call) <= 31  as call_in_31days
from test.paper;