How to delete series of observations

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

How to delete series of observations

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


Accepted Solutions
Solution
‎10-16-2014 05:48 AM
Super User
Super User
Posts: 7,955

Re: How to delete series of observations

Posted in reply to Kristinos

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


All Replies
Frequent Contributor
Posts: 144

Re: How to delete series of observations

Posted in reply to Kristinos

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

Super Contributor
Posts: 308

Re: How to delete series of observations

Posted in reply to Kristinos

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;

Solution
‎10-16-2014 05:48 AM
Super User
Super User
Posts: 7,955

Re: How to delete series of observations

Posted in reply to Kristinos

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;

Contributor
Posts: 25

Re: How to delete series of observations

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

VERY helpful!

Trusted Advisor
Posts: 1,228

Re: How to delete series of observations

Posted in reply to Kristinos

proc sql;

create table want as

select * from have

group by id

having sum(age>=4);

quit;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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