BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kristinos
Calcite | Level 5

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 Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

5 REPLIES 5
arodriguez
Lapis Lazuli | Level 10

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

Loko
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Kristinos
Calcite | Level 5

Thank you for all the good ideas!!! Smiley Happy

VERY helpful!

stat_sas
Ammonite | Level 13

proc sql;

create table want as

select * from have

group by id

having sum(age>=4);

quit;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 645 views
  • 6 likes
  • 5 in conversation