My data has multiple records per ID, where the date sometimes differs, and doesn't in other cases.
data have; input ID DATE varx; datalines; 1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B ; [5]
I want to select the earliest date of an ID, unless they have the same date, where I then want to select the record with "B". I'm not sure how to switch between criteria to first look at date, but then use different criteria if the date is the same.
Desired output:
ID DATE varx
1 01JAN2020 A
2 01MAR2020 B
data have;
input ID DATE :date9. varx $;
format DATE date9.;
datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;
proc sort data=have;
by ID DATE descending varx;
run;
data want;
set have_sorted;
by ID DATE descending varx;
if first.ID then output;
run;
Hi @MB_Analyst
Here is an approach to achieve this:
data have;
input ID DATE:date9. varx $;
format DATE date9.;
datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;
proc sort data=have out=have_sorted nodupkey;
by ID DATE descending varx;
run;
data want;
set have_sorted;
by ID DATE descending varx;
if first.ID then output;
run;
data have;
input ID DATE :date9. varx $;
format DATE date9.;
datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;
proc sort data=have;
by ID DATE descending varx;
run;
data want;
set have_sorted;
by ID DATE descending varx;
if first.ID then output;
run;
Hi @MB_Analyst Assuming your sample is a representative one and is already sorted in ascending Id Date var,
data have;
input ID DATE :date9. varx $1.;
format date date9.;
datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;
data want;
do _n_=1 by 1 until(last.id);
do until(last.date);
set have;
by id date;
end;
if _n_=1 then output;
end;
run;
ID | DATE | varx |
---|---|---|
1 | 01JAN2020 | A |
2 | 01MAR2020 | B |
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.