SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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 IDYearDateBalance

100

2013Jan 2xx
1302012Jan 4xx

130

2012Feb 6xx
1302013May 2xx
1302013Jun 7xx
1402014Mar 2xx
1402014Apr 9xx

I would only want to keep rows 1, 3, 5, and 7 in this example.  Any help is much appreciated!


Accepted Solutions
Solution
‎08-11-2015 12:56 PM
Super User
Posts: 19,770

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

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;

View solution in original post


All Replies
Valued Guide
Posts: 860

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

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

Occasional Contributor
Posts: 6

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

Posted in reply to Steelers_In_DC

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

Super User
Posts: 11,343

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

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;

Solution
‎08-11-2015 12:56 PM
Super User
Posts: 19,770

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

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;

Valued Guide
Posts: 860

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 463 views
  • 1 like
  • 4 in conversation