Date functions and logic

[KDang]

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!

[DBailey]

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;

[Ksharp]

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

[KDang]

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.

[KDang]

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.