Hi! I'm trying to subset some data to one row per id per week (currently is multiple rows/id/week). I want to keep the row associated with the latest date. Event date just corresponds to if the event occurred in 2021 (2) or before (1). Please help! Simple question but I'm super stuck! Thanks in advance!
Have:
week | id | year | event_date |
1 | 1 | 2015 | 1 |
1 | 1 | 2016 | 1 |
1 | 1 | 2021 | 2 |
2 | 1 | 2015 | 1 |
2 | 1 | 2016 | 1 |
2 | 1 | 2021 | 2 |
3 | 1 | 2015 | 1 |
3 | 1 | 2016 | 1 |
3 | 1 | 2021 | 2 |
1 | 2 | 2015 | 1 |
1 | 2 | 2016 | 1 |
1 | 2 | 2017 | 1 |
2 | 2 | 2015 | 1 |
2 | 2 | 2016 | 1 |
2 | 2 | 2017 | 1 |
3 | 2 | 2015 | 1 |
3 | 2 | 2016 | 1 |
3 | 2 | 2017 | 1 |
Want:
week | id | year | event_date |
1 | 1 | 2021 | 2 |
1 | 2 | 2017 | 1 |
2 | 1 | 2021 | 2 |
2 | 2 | 2017 | 1 |
3 | 1 | 2021 | 2 |
3 | 2 | 2017 | 1 |
Here you go:
data have;
infile datalines delimiter = ' ';
input week id year event_date;
datalines;
1 1 2015 1
1 1 2016 1
1 1 2021 2
2 1 2015 1
2 1 2016 1
2 1 2021 2
3 1 2015 1
3 1 2016 1
3 1 2021 2
1 2 2015 1
1 2 2016 1
1 2 2017 1
2 2 2015 1
2 2 2016 1
2 2 2017 1
3 2 2015 1
3 2 2016 1
3 2 2017 1
;
run;
proc sort data = have;
by week id year;
run;
data want;
set have;
by week id;
if last.id then output;
run;
Hopefully didn't miss something - let me know if I did.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: