topic Re: Find Minimum Date of Data in SAS Enterprise Guide

Find Minimum Date of Data

zdassu — Mon, 24 Apr 2017 11:25:29 GMT

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

Re: Find Minimum Date of Data

Jagadishkatam — Mon, 24 Apr 2017 11:36:37 GMT

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;

Re: Find Minimum Date of Data

thomp7050 — Mon, 24 Apr 2017 11:41:17 GMT

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;

Re: Find Minimum Date of Data

zdassu — Mon, 24 Apr 2017 11:54:43 GMT

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

Re: Find Minimum Date of Data

thomp7050 — Mon, 24 Apr 2017 12:00:39 GMT

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

Re: Find Minimum Date of Data

Kurt_Bremser — Mon, 24 Apr 2017 12:18:31 GMT

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.

Re: Find Minimum Date of Data

zdassu — Mon, 24 Apr 2017 12:21:55 GMT

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

Re: Find Minimum Date of Data

ShelleySessoms — Wed, 26 Apr 2017 19:19:17 GMT

Hi @zdassu,

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

Best,

Shelley