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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: