hi,
suppsoe I have the following table:
id | num |
1 | 0 |
2 | 0 |
2 | 0 |
3 | 1 |
3 | 0 |
4 | 1 |
4 | 0 |
5 | 1 |
First I would like to get the rows where the id has a num = 1 and this is easily done:
data have1;
set have;
if num = 1;
run;
and I get the following data:
id | num |
3 | 1 |
4 | 1 |
5 | 1 |
Now what I want is to get the ids 3,4 and 5 into a list like this when I run the following pseudo-code:
data want;
set have;
if id in list; I know this line has a wrong syntax, its just for the purpose of illustration
run;
I get the final data:
id | num |
3 | 1 |
3 | 0 |
4 | 1 |
4 | 0 |
5 | 1 |
So this data has all the original ids that have a num=1
Thnaks!
Use SQL and IN with a sub-query.
PROC SQL;
Select *
From have
Where id in (select distinct ID from have where num=1);
Quit;
Do both operations at once:
PROC SQL;
select * from have
group by id
having max(num=1)=1;
quit;
Hi PGStats,
thanks for the reply, but is it possible to have a solution with the list becasue the example that I have put is an oversimplification and my real data is more complicated than that, and it will be really helpful if I could create a data table by inputting a list.
Thank you!
Use SQL and IN with a sub-query.
PROC SQL;
Select *
From have
Where id in (select distinct ID from have where num=1);
Quit;
Hi Reeza,
I managed to put the values in a list in the following way:
proc sql;
select /*distinct*/ id
into :id separated by ', '
from have1
;
Then I wanted to use your code, which works greatly as it is, to do something like this:
PROC SQL;
Select *
From have
Where id in ( select &id);
Quit;
But I got an error message. Is it even possible to use list macro variables in this way?
Thank you!
change the where statement in your last step to:
where ID in (&id) /* drop the "select" */
You can, but you don't gain anything by using macro variables in this case.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: