Re-assign value based on dates

Question

I need to compare the dates and reassign the values to two new variables by ID.

If there are two dates for same id, then: If the 'date' variable is earlier, its value should be reassigned to "earlier status". If the 'date' variable is later, its value should be reassigned to "Current status".

if there is only one date for the id, the value will be reassigned to "current status". and the "earlier status" need to be missing.

if there are more than two dates for the id, then the value for the middle date will be ignored, and only use the earlier and most current value.

Any thoughts? Much appreciated!

This is the code that I have tried:

data origin;
input id date mmddyy8. status;
datalines;
1 1/1/2010 0
1 1/1/2011 1
2 2/2/2002 1
3 3/3/2003 1
3 2/5/2010 0
4 1/1/2000 0
4 1/1/2003 0
4 1/1/2005 1

;
run;

proc print; format date yymmdd8.; run;

proc sort data=origin out=a1;
by id date;
run;

data need; set a1;
if first.date then EarlierStatus=status;
else if last.date then CurrentStatus=status;
by id;
run;

proc print; format date yymmdd8.; run;

user_1623007 · Answer

So, a couple of things. First - note a few corrections to your code - in particular the : which is critical if you're going to input with mixed list style.

Second; you need to retain EarlierStatus. Otherwise it gets cleared out each data step iteration.

Third, you need to use first.id not first.date (and similar for last) - what first is doing there is saying "This is the first iteration of a new value of id". Date is what you'd say in English ("The first date for that...").

Finally, you need a couple of more tests to set your variables the way you have them.

data origin;
input id date :mmddyy10. status;
format date mmddyy10.;
datalines;
1 1/1/2010 0
1 1/1/2011 1
2 2/2/2002 1
3 3/3/2003 1
3 2/5/2010 0
4 1/1/2000 0
4 1/1/2003 0
4 1/1/2005 1

;
run;

proc sort data=origin out=a1;
by id date;
run;

data need; 
    set a1;
    by id;
    retain EarlierStatus;
    if first.id then call missing(EarlierStatus);   *first time through for an ID, clear EarlierStatus;
    if first.id and not last.id then EarlierStatus=status;  *if it is first time for the id, but not ONLY time, then set EarlierStatus;
    else if last.id then CurrentStatus=status;  *if it is last time for the id, then set CurrentStatus;
    if last.id then output;  *and if it is last time for the id, then output;
run;

The if/elses that I do there could be done slightly differently, depending on how you want to do things exactly, I was trying to keep things a bit direct as far as how they relate to each other.

user_13305416 · Answer

This proc sql will get what you want:

proc sql;
create table need as
select distinct
t1.id,
t2.EarlierStatus,
t1.CurrentStatus
from (select distinct
      id,
      date,
      status as CurrentStatus
      from origin
      group by id
      having date=max(date)) as t1
left join (select distinct
           id,
           date,
           status as EarlierStatus
           from origin 
           group by id
           having date ~= max(date)) as t2 on t1.id=t2.id;
quit;

The above code has two subqueries. In the first subquery, you retain only the rows with the max of date by id, and rename status to CurrentStatus. In the second subquery, you retain all the rows that do not have the max of date by id and rename status to EarlyStatus. So if your origin table has only one date for one id, it is also the max and you will delete this row in the second subquery. Then you perform a left join between the first and the second subqueries, pulling EarlyStatus from the second into the first query. If EarlyStatus is not found, then it goes missing.

Best,

Re-assign value based on dates

2 Answers