Please help me with code

Reply
Contributor
Posts: 39

Please help me with code

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!!!

Super User
Posts: 19,770

Re: Please help me with code

Proc sort data=have; by account date;

data want;

set have;

where amount >0;

by account;

if first.account;

run;

Trusted Advisor
Posts: 1,228

Re: Please help me with code

proc sql;

create table want as

select * from have

where amount ne 0

group by id

having date=min(date);

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 132 views
  • 2 likes
  • 3 in conversation