topic Using a proc sql into macro with in a where statement with a list of values in SAS Programming

Using a proc sql into macro with in a where statement with a list of values

Adnan2 — Tue, 09 Nov 2021 12:55:33 GMT

Hi All,

I have the following code and would like to do a search on values inside the macro &country. which has the value of Asia, Europe, USA.

I would like to use the newly created macro in a where statement in a separate data step. How could I change the value of the macro from Asia, Europe, USA to 'Asia', 'Europe', 'USA'

proc sql noprint;
 select distinct
 origin into:country separated by ", "
 from sashelp.cars
 ;
quit;

%put &country.;

proc sql;
 create table test_into as
 select 
 *
 from sashelp.cars
 where origin in ('&country.')
 ;
quit;

Many thanks in advance !

Re: Using a proc sql into macro with in a where statement with a list of values

PeterClemmensen — Tue, 09 Nov 2021 12:57:44 GMT

Try this

proc sql noprint;
 select distinct
 quote(strip(origin)) into:country separated by ", "
 from sashelp.cars
 ;
quit;

%put &country.;

proc sql;
 create table test_into as
 select 
 *
 from sashelp.cars
 where origin in (&country.)
 ;
quit;

Re: Using a proc sql into macro with in a where statement with a list of values

andreas_lds — Tue, 09 Nov 2021 13:03:16 GMT

Maybe i just don't understand your problem, but why do want all distinct values in a macro-variable? What is the purpose of subsequent steps?