Hi,
I want to delete all ID's that doesn't have observations after 4 years of age. How could I do that?
Data1:
id age obs
10 8 100
10 9 192
10 6 90
10 3 80
20 1 10
20 3 30
20 2 40
30 4 60
30 8 100
30 5 30
40 1 40
40 2 45
This is what I want (ID 20 and 40 are deleted):
ID age obs
10 8 100
10 9 192
10 6 90
10 3 80
30 4 60
30 8 100
30 5 30
Thanks
Simple sub-query:
proc sql;
create table WANT as
select *
from WORK.HAVE
where ID in (select distinct ID from WORK.HAVE where AGE >= 4);
quit;
Maybe not is the easier solution but I would do something like
proc sql norprint;
create table maxage as(
select ID, max(age) as max_age
from dataset
group by id
);
quit;
Doing this, you will get a dataset with
ID Max_age
10 9
20 3
30 8
40 2
Then I will merge with the original data and keep all that have max_age greater than 4 with a if or where condition
Hello,
data have;
input id age obs;
datalines;
10 8 100
10 9 192
10 6 90
10 3 80
20 1 10
20 3 30
20 2 40
30 4 60
30 8 100
30 5 30
40 1 40
40 2 45
;
data want;
merge have (in=a) have (in=b where=(dropage gt 4) rename=(age=dropage));
by id;
if a and b;
drop dropage;
run;
Simple sub-query:
proc sql;
create table WANT as
select *
from WORK.HAVE
where ID in (select distinct ID from WORK.HAVE where AGE >= 4);
quit;
Thank you for all the good ideas!!!
VERY helpful!
proc sql;
create table want as
select * from have
group by id
having sum(age>=4);
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.