The SAS Output Delivery System and reporting techniques

Last three rows

Reply
Super Contributor
Posts: 647

Last three rows

3710 16669129019 20110923 016669129019X99 3710 16669129019 20120927 016669129019X99 3710 16669129019 20121007 016669129019X99 3711 16129019 20110923 016669129019X99 3711 669129019 20120927 016669129019X99 3711 1666912 20121007 016669129019X99 3711 1666912 20121009 016669129019X99 3711 1666912 20121109 016669129019X99 How do we take a record that is last three records, based on date (the last record is always is the max date) if the last three records have the same date, choose only one record?

Contributor ndp
Contributor
Posts: 61

Re: Last three rows

Sort in the reverse order. create a record count variable and pick first 3. to remove duplicates using proc sort.

Trusted Advisor
Posts: 1,115

Re: Last three rows

I would suggest this, especially if your dataset is large so that you would be hesitant to read, let alone sort it:

 

data _null_;
if 0 then set have nobs=n;
call symputx('n',n-2);
stop;
run;

data want;
set have(firstobs=&n);
by date;
if last.date;
run;

This assumes that HAVE is sorted by variable DATE (and that this is the date variable you refer to). You didn't actually specify what to do if only two of the last three records have the same date. In this situation the above program would select the second observation of the date BY-group with two elements (and, of course, the observation from the other BY group). The data-_null_ step stores the number of observations in HAVE minus two in macro variable N, in order to let the second data step start reading at the last but two observation.

Valued Guide
Posts: 858

Re: Last three rows

Here is another solution:

 

data have;
infile cards dsd;
informat one $4. two $11. date yymmdd8. three $15.;
format one $4. two $11. date yymmddn8. three $15.;
input one$ two$ date$ three$;
cards;
3710,16669129019,20110923,016669129019X99
3710,16669129019,20120927,016669129019X99
3710,16669129019,20121007,016669129019X99
3711,16129019,20110923,016669129019X99
3711,669129019,20120927,016669129019X99
3711,1666912,20121007,016669129019X99
3711,1666912,20121009,016669129019X99
3711,1666912,20121109,016669129019X99
;

proc sort data=have;by descending date;

data count;
set have;
count + 1;
if count < 4 then output count;
run;

proc sort data=count out=want (drop=count) nodupkey;by date;

Respected Advisor
Posts: 3,124

Re: Last three rows

data count;
set have;
count + 1;
if count < 4 then output count;
else stop; /*this will stop SAS from reading in the whole dataset, even though you only need the first 3. Also, why involving another variable to count while you already have _n_?*/
run;
Super User
Posts: 5,071

Re: Last three rows

Perhaps even simpler:

 

data want;

set have (obs=3);

run;

Ask a Question
Discussion stats
  • 5 replies
  • 487 views
  • 1 like
  • 6 in conversation