topic Re: Last three rows in ODS and Base Reporting

Last three rows

SASPhile — Mon, 23 Nov 2015 16:58:13 GMT

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?

Re: Last three rows

ndp — Mon, 23 Nov 2015 17:08:24 GMT

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

Re: Last three rows

FreelanceReinh — Mon, 23 Nov 2015 18:03:46 GMT

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.

Re: Last three rows

Steelers_In_DC — Mon, 23 Nov 2015 18:29:10 GMT

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;

Re: Last three rows

Haikuo — Mon, 23 Nov 2015 19:25:37 GMT

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;

Re: Last three rows

Astounding — Mon, 23 Nov 2015 19:30:54 GMT

Perhaps even simpler:

data want;

set have (obs=3);

run;