topic Re: Date functions and logic in SAS Procedures

Date functions and logic

KDang — Wed, 27 Apr 2011 15:31:32 GMT

Hi,

I'm trying to determine an id's status based on if they have had any activity in the past 3 months.
My data is a log of all activity and looks like:
id LogIn
1 01Jan11
1 03Jan11
1 04Mar11
2 27Jan11
2 22Feb11
...

I want to be able to structure my data so it looks like the following:
id LogIn Status
1 01Jan11 Active
1 03Jan11 Active
1 04Mar11 Active
2 25Dec10 Inactive
2 27Jan11 Inactive
...

I think i need a function that checks whether or not the id has a Login date in the past 3 months, if yes, then status for id is set to Active.

I'm pretty sure it has to be a separate dataset to do the calculations then do a Merge to the main file.

Thanks in advance for your help!

Re: Date functions and logic

DBailey — Wed, 27 Apr 2011 16:07:05 GMT

proc sql;
select
t1.id,
t1.login,
case when intnx('day',date(),-90) >= t2.lastlogin then 'Active' else 'Inactive' as Current_Status
from work.logins t1 inner join (select id, max(login) as LastLogin from work.logins group by id) t2 on t1.id=t2.id;
quit;

Re: Date functions and logic

Ksharp — Thu, 28 Apr 2011 05:25:16 GMT

You can use Hash Table.
But I do not understand

2 25Dec10 Inactive
2 27Jan11 Inactive

Why they are inactive. dec10 is also included in past 3 month for jan11?

Ksharp

Re: Date functions and logic

KDang — Thu, 28 Apr 2011 13:28:58 GMT

Thanks for the replies.

I'm trying to make DBaily's statements work, but havent gotten it to work yet.

Ksharp,
I'm practically trying to figure out how long since todays date was their last Login, so if its been longer than 90 days than their status would be inactive across all rows with that persons id.

Re: Date functions and logic

KDang — Thu, 28 Apr 2011 14:10:39 GMT

Ok I think i've figured it out.

Essentially what I used was
INTCK('day',Login,Today())
function to get the diff between the two dates then if value is less than 90 i set to Active, else Inactive.