DATA Step, Macro, Functions and more

Case Statement logic

Reply
Contributor
Posts: 26

Case Statement logic

[ Edited ]

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. 

Super User
Posts: 19,861

Re: Case Statement logic

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. 

PROC Star
Posts: 1,760

Re: Case Statement logic

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

 

 

Respected Advisor
Posts: 4,931

Re: Case Statement logic

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;
PG
Ask a Question
Discussion stats
  • 3 replies
  • 305 views
  • 3 likes
  • 4 in conversation