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