output of a proc sql used in a where clause in a "data step"

[drkd]

Hi Gurus!

How do I use the output of the proc sql  into the next datastep?

%macro m;

  proc sql;select distinct col1 from T;quit;

%mend  m;

data in; set in;where col2 in (%m);run;

fyi -    I tried RESOLVE('%m') too.

What do I need to do to make this logic work? 

Thanks in advance.

[ballardw · Accepted solution]

What you are attempting fails because when a step boundary such as a RUN, DATA or PROC statement is encountered that is considered the end of the code for a data step.

So you basically cannot embed Proc SQL code into a data step.

I think that you may be looking for something like (untested):

proc sql;
   create table want as
   select a.*
   from in as a
        right join
        (select distinct col1 from t) as b
        on a.col2=b.col1
   ;
quit;

One of the major strengths of SQL is to combine or select data based on values in two or more data sets. A key part is to identify what type of JOIN (there are several) and the conditions.

You may get better results by posting example start data and the desired result. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

BTW, while the syntax DATA in ; Set in; is legal it is a very poor general approach to coding as if you code had executed but done something undesired you have replaced the data set IN and would have to back to earlier steps to recreate the data.

Especially with very experimental code you are likely to damage the data.

[heffo]

Another way would be to skip the join and just use a "where in " with a sub query.

data T;
	length Col1 $ 10;
	input Col1;
datalines;
Alice
James
Judy
Ronald
;
run;

proc sql;
   create table want as
   select *
   from SAShelp.class 
   where Name in (select distinct Col1 from T)   ;
quit;

.