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;
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!
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.