BookmarkSubscribeRSS Feed
Subbarao
Fluorite | Level 6

I have data as below, I may have each record for JAN, APR and JUL for each id or may not, I need to get id with score greater than zero with recent proceed date. 

From below three records, code has to pull Apr record, because for id 123 APR is the record with amount greater than zero and recently processed

Id            date       amount

123        20140701     0

123        20140401     15

123        20140101     30

by considering another example

Id            date             amount

321  20140701            30

321  20140401            15

321  20140101             0

456  20140401             0

456  20140701             0

456  20140101             45

789  20140701             15

789 20140101              0

From above input data, output should be

Account     date           amount

321         20140401             15

456         20140101             45

789         20140701             15

Because for the id 321, Apr record is the recently processed one with amount is greater than zero, and for the id 456 Jan record is the one with recently processed with amount greater than zero.

Hope you understood my question, please help me with solution. THANKS IN ADVANCE!!!

2 REPLIES 2
Reeza
Super User

Proc sort data=have; by account date;

data want;

set have;

where amount >0;

by account;

if first.account;

run;

stat_sas
Ammonite | Level 13

proc sql;

create table want as

select * from have

where amount ne 0

group by id

having date=min(date);

quit;

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 364 views
  • 2 likes
  • 3 in conversation