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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1238 views
  • 1 like
  • 4 in conversation