topic Re: Capturing the first and last date record into new tables in SAS Programming

Capturing the first and last date record into new tables

Sas_Act_114 — Wed, 08 May 2019 19:49:56 GMT

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!

Re: Capturing the first and last date record into new tables

PaigeMiller — Wed, 08 May 2019 19:52:25 GMT

data dataset1 dataset2;
    set have;
    by id;
    if first.id then output dataset1;
    if last.id then output dataset2;
run;

Re: Capturing the first and last date record into new tables

data_null__ — Wed, 08 May 2019 19:58:44 GMT

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;

Re: Capturing the first and last date record into new tables

novinosrin — Wed, 08 May 2019 20:12:27 GMT

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;

Re: Capturing the first and last date record into new tables

Sas_Act_114 — Wed, 08 May 2019 20:13:18 GMT

Thank you, this did exactly what I was looking for, much appreciated.