BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sas_Act_114
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
data dataset1 dataset2;
    set have;
    by id;
    if first.id then output dataset1;
    if last.id then output dataset2;
run;

    

--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26
data dataset1 dataset2;
    set have;
    by id;
    if first.id then output dataset1;
    if last.id then output dataset2;
run;

    

--
Paige Miller
Sas_Act_114
Fluorite | Level 6

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

data_null__
Jade | Level 19

 

 

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;
novinosrin
Tourmaline | Level 20
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1834 views
  • 1 like
  • 4 in conversation