DATA Step, Macro, Functions and more

putting row values into a list

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

putting row values into a list

[ Edited ]

 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!


Accepted Solutions
Solution
‎09-17-2016 09:14 PM
Super User
Posts: 19,861

Re: putting row values into a list

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,931

Re: putting row values into a list

Do both operations at once:

 

PROC SQL;
select * from have
group by id
having max(num=1)=1;
quit;
PG
Super Contributor
Posts: 441

Re: putting row values into a list

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!

Solution
‎09-17-2016 09:14 PM
Super User
Posts: 19,861

Re: putting row values into a list

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;
Super Contributor
Posts: 441

Re: putting row values into a list

[ Edited ]

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!

Trusted Advisor
Posts: 1,584

Re: putting row values into a list

change the where statement in your last step to:

where ID in (&id)        /* drop the "select" */

Super User
Posts: 19,861

Re: putting row values into a list

You can, but you don't gain anything by using macro variables in this case. 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 416 views
  • 4 likes
  • 4 in conversation