topic How to delete old entries (and keep only newest) for client transactions in SAS Data Management

How to delete old entries (and keep only newest) for client transactions

bpearce — Tue, 11 Aug 2015 13:00:29 GMT

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!

Re: How to delete old entries (and keep only newest) for client transactions

Steelers_In_DC — Tue, 11 Aug 2015 16:37:03 GMT

Why do you want 3? By your logic I think you want line 1,2,4,6.

Re: How to delete old entries (and keep only newest) for client transactions

ballardw — Tue, 11 Aug 2015 16:54:53 GMT

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;

Re: How to delete old entries (and keep only newest) for client transactions

Reeza — Tue, 11 Aug 2015 16:56:37 GMT

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;

Re: How to delete old entries (and keep only newest) for client transactions

Steelers_In_DC — Tue, 11 Aug 2015 16:58:33 GMT

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;

Re: How to delete old entries (and keep only newest) for client transactions

bpearce — Tue, 11 Aug 2015 17:55:57 GMT

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