topic Case Statement logic in SAS Programming

Case Statement logic

itshere — Fri, 12 Aug 2016 01:28:48 GMT

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.

Re: Case Statement logic

Reeza — Fri, 12 Aug 2016 01:32:49 GMT

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.

Re: Case Statement logic

ChrisNZ — Fri, 12 Aug 2016 01:38:37 GMT

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

Re: Case Statement logic

PGStats — Fri, 12 Aug 2016 02:14:08 GMT

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;