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
Ref | Date |
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 |
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;
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;
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;
Thank you all for all your help I have used thomp7050 solution and it has worked perfectly
LOL! In the future I recommend you mark the actual solution as the solution, and not your own post.
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
Hi @zdassu,
I marked the actual solution as correct for you. I understand an errant slip of the finger!
Best,
Shelley
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.