Help using Base SAS procedures

Finding the first and last date in dataset

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Finding the first and last date in dataset

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


Accepted Solutions
Solution
‎12-16-2011 08:00 PM
Super Contributor
Posts: 1,636

Re: Finding the first and last date in dataset

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

View solution in original post


All Replies
PROC Star
Posts: 7,467

Finding the first and last date in dataset

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;

Contributor
Posts: 71

Re: Finding the first and last date in dataset

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 Smiley Happy

SAS Employee
Posts: 416

Finding the first and last date in dataset

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;

Contributor
Posts: 71

Re: Finding the first and last date in dataset

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

PROC Star
Posts: 7,467

Finding the first and last date in dataset

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=_Smiley Happy;

  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;

Super User
Super User
Posts: 7,039

Finding the first and last date in dataset

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.

Solution
‎12-16-2011 08:00 PM
Super Contributor
Posts: 1,636

Re: Finding the first and last date in dataset

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

Contributor
Posts: 71

Re: Finding the first and last date in dataset

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

Smiley Happy

Contributor
Posts: 71

Re: Finding the first and last date in dataset

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 16070 views
  • 9 likes
  • 5 in conversation