Finding the first and last date in dataset

[HyunJee]

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

[art297]

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;

[HyunJee]

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

[udo_sas]

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;

[HyunJee]

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

[art297]

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;

[Tom]

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.

[Linlin · Accepted solution]

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

[HyunJee]

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

[HyunJee]

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