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!!!
Proc sort data=have; by account date;
data want;
set have;
where amount >0;
by account;
if first.account;
run;
proc sql;
create table want as
select * from have
where amount ne 0
group by id
having date=min(date);
quit;
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.
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.