How can we keep the most recent data with proc sort?

[GN0001]

Dear team member,

Thanks for your post. Thanks for this approach. I can use proc sql to do get what I want. How about proc sort nodupkey approach?

Thanks,

blueblue

[GN0001]

Hello Kurt,
I think this works if I sort ascending by date, no dupkey. I need to make sure which observation SAS keeps after I sort it by ascending.
if I have:
1 03/14/2021
1 04/14/2021
1 08/09/2021
Then when I use proc sql no dupkey ascending, then I will get 08/09/2021, SAS doesn't remove the max date? I want to know how SAS processes the observations in proc sort nodupkey ascending.
Please advise me.
Regards,
bluebue

[Patrick · Accepted solution]

@GN0001 IF you would have posted sample data with the date variable containing a proper SAS date value then I assume the discussion here would have been much shorter/quicker.

IF you've got a date variable that sorts properly then a double Proc Sort is one way to go.

The first Proc Sort to order your data with the newest date per member id on top, the 2nd Proc Sort with a NODUPKEY to then pick the first row per member id (same that what a data step if first.member_id would do).

data have;
  input MemberID $ date1:ddmmyy10.;
  format date1 date9.;
  datalines;
1 04/02/2021
1 04/03/2021
1 05/03/2019
;

proc sort data=have out=want;
  by MemberID DESCENDING date1;
run;
proc sort nodupkey out=want;
  by MemberID;
run;

[GN0001]

Sorry, please see my response to Kurt.
Can you please read
My response to Kurt. Did you see what Kurt had responded?
Respectfully
Blueblue

[GN0001]

Hello Patrick,
Something came to my mind. If memberId are same but observations are different across rows, then what happens?
Thanks for your input,
Blueblue

[Kurt_Bremser]

---

@GN0001 wrote:
Hello Patrick,
Something came to my mind. If memberId are same but observations are different across rows, then what happens?
Thanks for your input,
Blueblue

---

Once again: try it.

Especially test PROC SORT with the NODUPKEY vs. the NODUPREC options.

[Kurt_Bremser]

Using the double SORT may not work under certain circumstances (SPDE datasets, for example).

After the ascending sort, do

data want;
set have;
by id;
if last.id;
run;

[GN0001]

Hello Kurt, I found what I was looking for, so if it keeps the max in the first observation, this should work.
Thanks for your response.
By the way, I posted my data very first time I posted this question.
I truly appreciate your response.
Thanks,
Blue blue

[GN0001]

Kurt,

Can you please explain about this?
thanks

Blue blue

[Kurt_Bremser]

TRY.

IT.