I have a very large table with ID numbers and dates associated with them. As as example of a table with two columns (I will use names instead of ID numbers for simplicity):
John 1/1/2010
John 1/1/2011
John 1/1/2012
John 1/1/2013
Sam 6/1/2012
Sam 4/1/2013
Sam 4/1/2014
Victor 7/1/2011
Zack 4/1/2011
Zack 4/1/2012
Zack 10/1/2012
Zack 10/1/2013
From this table, I want to make two tables - One that has the name and the earliest date associated to that name (member ID) and one that has the name and the last date associated to that name. Thus the first table would come out as:
John 1/1/2010
Sam 6/1/2012
Victor 7/1/2011
Zack 4/1/2011
And the second table (with the last date) would come out as:
John 1/1/2013
Sam 4/1/2014
Victor 7/1/2011
Zack 10/1/2013
I've included the example of "Victor" as there are records with just one date, thus the same date would have to count as both the first and last. Thank you to any that see this and can help!
data dataset1 dataset2;
set have;
by id;
if first.id then output dataset1;
if last.id then output dataset2;
run;
data dataset1 dataset2;
set have;
by id;
if first.id then output dataset1;
if last.id then output dataset2;
run;
Thank you, this did exactly what I was looking for, much appreciated.
data dates;
input name:$8. date:mmddyy.;
format date mmddyy.;
cards;
John 1/1/2010
John 1/1/2011
John 1/1/2012
John 1/1/2013
Sam 6/1/2012
Sam 4/1/2013
Sam 4/1/2014
Victor 7/1/2011
Zack 4/1/2011
Zack 4/1/2012
Zack 10/1/2012
Zack 10/1/2013
;;;;
run;
proc summary data=dates nway missing;
class name;
output out=want(drop=_) min(date)=first max(date)=last;
run;
proc print;
run;
data have;
input name:$8. date:mmddyy.;
format date mmddyy.;
cards;
John 1/1/2010
John 1/1/2011
John 1/1/2012
John 1/1/2013
Sam 6/1/2012
Sam 4/1/2013
Sam 4/1/2014
Victor 7/1/2011
Zack 4/1/2011
Zack 4/1/2012
Zack 10/1/2012
Zack 10/1/2013
;;;;
run;
proc sql;
create table earliest_date as
select *
from have
group name
having date=min(date);
quit;
proc sql;
create table latest_date as
select *
from have
group name
having date=max(date);
quit;
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.