screen each observation

[knighsson]

Hi, 

I tried to screen each observation in my data set and find out the participants (same ID) that meet a standard to generate a new dataset. The standard is variable 1 = 6 or 7. So as long as the participant has one observation meeting the standard, all the observations of that participant will keep in the new dataset. Can you help me with how to do it?  Thanks.

The original data looks like:

ID.       variable 1.   variable2-20

1.              1        .......

1               3        .......

2                7        .......

3                 5        .......

3                 3        .......

3                  7          .......

...                ...           .......

the new dataset should look like:

ID.       variable 1.   variable2-20

2                7        .......

3                 5        .......

3                 3        .......

3                  7          .......

...                ...           .......

[ballardw]

One way might be

proc sql;
   create table want as 
   select b.* 
   from (select distinct id from have
         where variable1 in (6,7) ) as a
        left join
        have as b
        on a.id = b.id
   ;
quit;

[knighsson]

Thank you so much! This is really helpful!

I have a follow-up question: if the standard is that variable1 =1-5 and 6-7, which means each participant in "want" at least has (one observation that its variable1 is 1or 2 or 3 or 4 or 5) AND (another observation that its variable 1= 6 or 7)

How should I change the code?
Thank you!

[Reeza · Accepted solution]

Still SQL

proc sql; 
create table want as
select *
from have
where ID in (select distinct ID from have where variable1 in (6,7));
quit;

[knighsson]

Thank you so much! This is very helpful!

I have a follow-up question: if the standard is that variable1 =1-5 and 6-7, which means each participant in "want" at least has (one observation that its variable1 is 1or 2 or 3 or 4 or 5) AND (another observation that its variable 1= 6 or 7)

How should I change the code?
Thank you!

[ed_sas_member]

Hi @knighsson 

You can adapt the code as follows (multiple merging):

proc sql;
	create table want as
	select c.* 
	from ((select distinct id
				 from have
				 where variable1 in (1,2,3,4,5)) as a
         inner join
		 (select distinct id
		 		 from have
				 where variable1 in (6,7)) as b
         on a.id = b.id)
		 left join
		 have as c
		 on a.id = c.id
   ;
quit;

Hope this helps!

Best,

[knighsson]

Thank you！This is very helpful！