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.
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.