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

Hi can some one help me I want to bring back the minimum dates for each Ref, please see the table below.

 

I am using SAS Enterprise Guide 7.1

 

 Table_Refs

RefDate
101/01/2017
102/02/2017
201/02/2016
202/03/2016
203/04/2016
204/05/2016
310/04/2015
311/09/2015
404/02/2014
401/02/2016
401/01/2017

 

1 ACCEPTED SOLUTION

Accepted Solutions
thomp7050
Pyrite | Level 9

Alternative, which may be copied and pasted to be used in several systems (e.g. R, SPSS, etc.):

 

data have;
infile cards missover;
input Ref	Date:mmddyy10.;
format date date9.;
cards;
1	01/01/2017
1	02/02/2017
2	01/02/2016
2	02/03/2016
2	03/04/2016
2	04/05/2016
3	10/04/2015
3	11/09/2015
4	04/02/2014
4	01/02/2016
4	01/01/2017
;
run;

PROC SQL;
SELECT REF, MIN(DATE) FORMAT=MMDDYY10. FROM HAVE GROUP BY REF;
QUIT;

View solution in original post

7 REPLIES 7
Jagadishkatam
Amethyst | Level 16

Please try the proc sort and first. approach to get the minimum date for each ref

 

data have;
infile cards missover;
input Ref	Date:mmddyy10.;
format date date9.;
cards;
1	01/01/2017
1	02/02/2017
2	01/02/2016
2	02/03/2016
2	03/04/2016
2	04/05/2016
3	10/04/2015
3	11/09/2015
4	04/02/2014
4	01/02/2016
4	01/01/2017
;
run;

proc sort data=have;
by ref date;
run;

data want;
set have;
by ref date;
if first.ref;
run;


Thanks,
Jag
thomp7050
Pyrite | Level 9

Alternative, which may be copied and pasted to be used in several systems (e.g. R, SPSS, etc.):

 

data have;
infile cards missover;
input Ref	Date:mmddyy10.;
format date date9.;
cards;
1	01/01/2017
1	02/02/2017
2	01/02/2016
2	02/03/2016
2	03/04/2016
2	04/05/2016
3	10/04/2015
3	11/09/2015
4	04/02/2014
4	01/02/2016
4	01/01/2017
;
run;

PROC SQL;
SELECT REF, MIN(DATE) FORMAT=MMDDYY10. FROM HAVE GROUP BY REF;
QUIT;
zdassu
Quartz | Level 8

Thank you all for all your help I have used thomp7050 solution and it has worked perfectly

thomp7050
Pyrite | Level 9

LOL!  In the future I recommend you mark the actual solution as the solution, and not your own post.Smiley Happy

zdassu
Quartz | Level 8

Lol I will do mate, I just realised after, as this is the first time I have posted on here. i feel like a right old plonker

ShelleySessoms
Community Manager

Hi @zdassu,

 

I marked the actual solution as correct for you. I understand an errant slip of the finger!

 

Best,

Shelley

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Kurt_Bremser
Super User

Still another method, using the MEANS procedure:

proc means data=have noprint nway;
class ref;
var date;
output out=want (drop=_type_ _freq_) min(date)=date;
run;

With large datasets, sorting and using "by" instead of "class" might become necessary if you run out of memory. As long as the cardinality of ref does not exceed memory limitations, class works without prior sorting.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 19158 views
  • 2 likes
  • 5 in conversation