topic Finding the first and last date in dataset in SAS Procedures

Finding the first and last date in dataset

HyunJee — Fri, 16 Dec 2011 15:51:33 GMT

I am trying to find what the older and most recent dates in a dataset are. I am not concerend with the first and last dates by ID, but simply the first date (one line, one date) and the last date (one line, one date) in the data file.

Is there any way to do this? Only two lines of data would be needed, one line with the first date and a second line with the last date.

Thank you for any help you can provide.

HyunJee

Finding the first and last date in dataset

art297 — Fri, 16 Dec 2011 15:58:09 GMT

data have;

informat thedates date9.;

format thedates date9.;

input thedates;

cards;

01dec2011

02dec2011

03dec2011

04dec2011

05dec2011

06dec2011

07dec2011

08dec2011

;

proc sql;

create table want as

select distinct thedates

from have

having thedates=min(thedates) or

thedates=max(thedates)

;

quit;

Finding the first and last date in dataset

udo_sas — Fri, 16 Dec 2011 18:45:05 GMT

If you have access to SAS/ETS by any means you might want to consider using PROC TIMESERIES as well.

Thanks,

Udo

data have;

informat thedates date9. other;

format thedates date9.;

input thedates other;

cards;

01dec2011 1

02dec2011 1

03dec2011 1

04dec2011 1

05dec2011 2

06dec2011 2

07dec2011 2

08dec2011 2

;

proc timeseries data=have out=_null_ outsum=need(keep=_name_ start end);

id thedates interval=day;

var other;

run;

Finding the first and last date in dataset

art297 — Fri, 16 Dec 2011 21:35:45 GMT

I may have misunderstood the question. But, regardless of whether I did or didn't, an alternative solution would be to just use a datastep. E.g., if I was wrong and you only want the first and last records, then the following might suffice:

data want;

set have end=last;

if _n_ eq 1 or last then output;

run;

Conversely, if you actually do need the minimum and maximum dates in the file, then you could use something like:

data want (drop=_:);

set have end=last;

retain _maxdate _mindate;

_mindate=min(_mindate,thedates);

_maxdate=max(_maxdate,thedates);

if last then do;

thedates=_mindate;

output;

thedates=_maxdate;

output;

end;

run;

Finding the first and last date in dataset

Tom — Sat, 17 Dec 2011 00:28:36 GMT

Here is a way using PROC SQL. But if there is only one date value you will get a dataset with only one observation.

proc sql noprint ;

create table minmax as select distinct DATE

from have having DATE= min(DATE) or DATE= max(DATE)

order by 1

;

quit;

You could pull out min and max as two columns and then transpose.

Re: Finding the first and last date in dataset

Linlin — Sat, 17 Dec 2011 01:00:53 GMT

data have;

informat thedates date9.;

format thedates date9.;

input thedates;

cards;

01dec2011

02dec2011

03dec2011

04dec2011

05dec2011

06dec2011

07dec2011

08dec2011

;

proc sql;

create table want as

select min(thedates) as min_date format=date9.,

max(thedates) as max_date format=date9.

from have;

quit;

proc print; title from dataset &syslast;run;

proc transpose data=want out=new(rename=(_name_=date)) ;

run;

proc print; title from dataset &syslast;run;

Linlin

Re: Finding the first and last date in dataset

HyunJee — Mon, 19 Dec 2011 16:50:15 GMT

This code works great. This is a correct answer also, but I could only make as helpful.

Thank you for your help and providing an example code. Extremely helpful

Re: Finding the first and last date in dataset

HyunJee — Mon, 19 Dec 2011 16:51:35 GMT

I like how this code prints out the min and max date of interest. Thank you very much for providing the example code!

Re: Finding the first and last date in dataset

HyunJee — Mon, 19 Dec 2011 16:52:48 GMT

The solution you provided was helpful also and provided the needed result. Thank you!

Re: Finding the first and last date in dataset

HyunJee — Mon, 19 Dec 2011 16:53:16 GMT

Thank you to everyone for you input and assistance. Extremely helpful!!!