topic SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date) in SAS Data Management

SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

GregG — Mon, 16 Nov 2015 18:03:01 GMT

I have a table that has ID, Code, and Date. Each ID can have multiple codes, each occurring on a different date. I only want the ID & Code for the most recent date.

How do I do this?

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

LinusH — Mon, 16 Nov 2015 18:31:03 GMT

Two (of many more perhaps) choices:

First sort, like in your post title. The a new sort on ID alone, and nodupkey.
Using Join transformation, Group By on ID, having date = max(date)

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

GregG — Mon, 16 Nov 2015 18:48:31 GMT

Thank you! I was thinking about the double sort, but wasn't sure if the second sort would re-sort (and wipe out the first).

Ended up going with the join transformation option instead.

Thank you again!

Re: SAS DI Studio - Sort by ID and Desc Date - Keep only 1 obs per ID (with most recent date)

LinusH — Mon, 16 Nov 2015 19:57:46 GMT

Happy to help 😊
But the double sort should be reliable. The PROC SORT doc states that NODUPKEY will keep the first row encountered for each by group.
What to chose is just matter of taste. The SQL alternative could perform better if your source table is stored in a powerful RDBMS server.