BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bpearce
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Steelers_In_DC
Barite | Level 11

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

bpearce
Calcite | Level 5

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

ballardw
Super User

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;

Reeza
Super User

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;

Steelers_In_DC
Barite | Level 11

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

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