My data contains many rows, with multiple rows for some ids. I would like to create a new date variable based on a date column I have, such that all rows with the same id will display the EARLIEST date for that id. I've attached an example of what I mean.
What would be the best way to accomplish this? Do I need to use PROC SQL and a join, or is there an easy way to do this in SAS?
data want;
set have;
by id;
retain need;
if first.id then need=date;
run;
Sorry, I realized after trying your solution that there is one more element I forgot in the sample. I uploaded a new one.
My issue is it may not actually be the earliest date for that id in the whole dataset, but I need the earliest date for each id where a certain indicator=1.
This code doesn't work, but the idea would be something similar to:
data want;
set have;
by id;
retain need;
if first.id AND indicator=1 then need=date;
else need=.;
run;
Hi @ESpiel Alright please try this-
data want;
set have;
by id;
retain need;
if first.id then need=.;
if not need and indicator=1 then need=DATE_HAVE;
format need mmddyy10.;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.