Trying to get this manipulation going and any thoughts/help on this is appreciated.
Dataset Sample
id status date
1 OK 02-MAY-09
1 OK 10-MAY-09
1 DEL 01-JUL-22
1 OK 01-DEC-22
1 OK 04-DEC-22
1 DEL 06-DEC-22
2 DEL 01-JAN-23
3 OK 03-JAN-23
4 DEL 04-JAN-23
4 DEL 05-JAN-23
4 OK 06-JAN23
Looking for the following output which can be long or wide format
1 OK 02-MAY-09
1 DEL 01-JUL-22
1 OK 01-DEC-22
1 DEL 06-DEC-22
2 DEL 01-JAN-23
3 OK 03-JAN-23
4 DEL 04-JAN-23
4 OK 06-JAN23
Thanks in advance.
Same selection logic as already suggested by @jins
data have;
infile datalines truncover;
input id status $ dt:date11.;
format dt date9.;
datalines;
1 OK 02-MAY-09
1 OK 10-MAY-09
1 DEL 01-JUL-22
1 OK 01-DEC-22
1 OK 04-DEC-22
1 DEL 06-DEC-22
2 DEL 01-JAN-23
3 OK 03-JAN-23
4 DEL 04-JAN-23
4 DEL 05-JAN-23
4 OK 06-JAN23
;
/* ensure source data is sorted as required */
proc sort data=have presorted;
by id dt;
run;
data want;
set have;
by id status notsorted;
if first.status;
run;
proc print data=want;
run;
Can you explain the logic for choosing the rows to output?
I should have mentioned that oops....
To obtain the date of the first occurrence between the status change by ID. For example ID-1 has two OK status before the DEL status, so we keep the first one with date 02-May-2009. For ID's with no status changes, it is output as is (ID-2).
Is the notsorted option in the by variable a good idea to filter them out if the data is already sorted prior by id, date and status?
data check;
by id status notsorted;
if first.status=1;
run;
Same selection logic as already suggested by @jins
data have;
infile datalines truncover;
input id status $ dt:date11.;
format dt date9.;
datalines;
1 OK 02-MAY-09
1 OK 10-MAY-09
1 DEL 01-JUL-22
1 OK 01-DEC-22
1 OK 04-DEC-22
1 DEL 06-DEC-22
2 DEL 01-JAN-23
3 OK 03-JAN-23
4 DEL 04-JAN-23
4 DEL 05-JAN-23
4 OK 06-JAN23
;
/* ensure source data is sorted as required */
proc sort data=have presorted;
by id dt;
run;
data want;
set have;
by id status notsorted;
if first.status;
run;
proc print data=want;
run;
thanks.
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.