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?
Sort in the reverse order. create a record count variable and pick first 3. to remove duplicates using proc sort.
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.
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;
Perhaps even simpler:
data want;
set have (obs=3);
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.