I have the following dataset where I need to only have the first year (occurrence) where the count is >=2 (I also want to keep any subsequent years thereafter). For instance for ID1, I need data starting in FY12 and I also want to keep FY 13 - 15 because I know the index year for this ID is FY12. I would keep ID2 and keep ID3 starting at FY12.
I can't figure out quite how to keep everything I need. Any suggestions?
ID Count FY
1 1 10
1 1 11
1 3 12
1 1 13
1 12 14
1 2 15
2 2 11
3 1 11
3 7 12
3 2 13
3 1 14
Assuming your data is in sorted order as shown:
data want;
set have;
by id;
if first.id then output_flag=0;
if count >= 2 then output_flag + 1;
if output_flag;
drop output_flag;
run;
one way to do this
data have;
input @1 ID @5 Count @15 FY;
datalines;
1 1 10
1 1 11
1 3 12
1 1 13
1 12 14
1 2 15
2 2 11
3 1 11
3 7 12
3 2 13
3 1 14
;
proc sql;
create table want as
select a.* from
(select * from have)a
inner join
(select id, min(FY) as min_fy from have
where count >= 2
group by id)b
on a.id = b.id
and a.fy >= b.min_fy;
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.