topic Re: screen each observation in SAS Programming

screen each observation

knighsson — Tue, 05 May 2020 20:15:48 GMT

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 .......

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

Re: screen each observation

ballardw — Tue, 05 May 2020 20:35:49 GMT

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;

Re: screen each observation

Reeza — Tue, 05 May 2020 20:55:42 GMT

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;

Re: screen each observation

knighsson — Tue, 05 May 2020 21:21:28 GMT

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!

Re: screen each observation

knighsson — Tue, 05 May 2020 21:25:59 GMT

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!

Re: screen each observation

ed_sas_member — Wed, 06 May 2020 08:04:49 GMT

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,

Re: screen each observation

knighsson — Thu, 07 May 2020 18:43:42 GMT

Thank you！This is very helpful！