Hi,
I have a dataset of 43000 entries for client transactions. I want to only keep the newest transaction, by year, for each client and remove all of the others from my data set. For example, if a client has two transactions in 2012 and 3 in 2013 I want to keep the newest transaction from 2012 and the newest from 2013.Here is an example of what I have
Client ID | Year | Date | Balance |
---|---|---|---|
100 | 2013 | Jan 2 | xx |
130 | 2012 | Jan 4 | xx |
130 | 2012 | Feb 6 | xx |
130 | 2013 | May 2 | xx |
130 | 2013 | Jun 7 | xx |
140 | 2014 | Mar 2 | xx |
140 | 2014 | Apr 9 | xx |
I would only want to keep rows 1, 3, 5, and 7 in this example. Any help is much appreciated!
Assuming newest means latest/last in the year this should do it.
proc sort data=have;
by client_id year date;
run;
data want;
set have;
by client_id year date;
if last.year;
run;
Why do you want 3? By your logic I think you want line 1,2,4,6.
Hi Mark,
I would want lines 3, 5, and 7 because those transactions all occurred after lines 2, 4, and 6. (Feb 6, 2012 is after Jan 4, 2012).
Thanks,
Brad
Is date a character or numeric variable? If it would sort properly, OR your data is already sorted by Client Id, Year and date then
Data want;
set have;
by ClientId Year date;
if last.year;
run;
Assuming newest means latest/last in the year this should do it.
proc sort data=have;
by client_id year date;
run;
data want;
set have;
by client_id year date;
if last.year;
run;
I you agree with my output here is my solution. It must be sorted by ID Year:
Data have;
infile cards dsd;
input Client Year Date$ Balance$;
cards;
100,2013,Jan 2,xx
130,2012,Jan 4,xx
130,2012,Feb 6,xx
130,2013,May 2,xx
130,2013,Jun 7,xx
140,2014,Mar 2,xx
140,2014,Apr 9,xx
;
data want;
set have;
by client year notsorted;
if first.year then output;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.