CODEWITHSUNDEEP
sas
Dataminer
Dataminer

Reputation: 1549

Accessing Macro variables using Proc sql

I am using the following code to generate a macro variable:

proc sql;
   select count(*) into :Nobs 
   from table1;
   select variable_list into :val1-:val%left(&Nobs) 
   from table1;
quit;

I would now like to use this variable list to subset a particular data set. I could do this with individual macro variables as follows:

data subset_data;
   set original_data;
   where variable = "&val1";
run;

My question is, in the where condition how could I check for all the values in the val array?

Upvotes: 0

Views: 387

Answers (1)

Tom
Tom

Reputation: 51621

You probably want to use the IN () operator. "Arrays" of macro variables are usually more work than they are worth. Instead just store all of the values into a single macro variable. If your values are numbers then use this:

proc sql noprint;
select variable
  into :value_list separated by ' ' 
  from table1
;
quit;
data subset_data;
  set original_data;
  where variable in (&value_list);
run;

If your variables are character variables then use the QUOTE() function so that you generate quoted strings instead of numbers.

select quote(trim(variable))
  into :value_list separated by ' ' 
  from table1
;

Upvotes: 1

Related Questions