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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.