Accessing Macro variables using Proc sql

Question

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?

Your question is not clear. &val1-&val2 will have values. How do you want to check those values? Suggest you show the data in table1. And show the SAS data step code you would like to generate. — Quentin
– Quentin, Commented Sep 8, 2015 at 11:07
There is no need to pre-count the number of values. You can use :val1- to indicate an unbounded list of macro variables. The automatic macro variable SQLOBS will have the count if you need it for something else. If you have an old version of SAS that does not support that syntax then just use an upper bound that is larger than any possible count. :var1-:var99999 — Tom
– Tom, Commented Sep 8, 2015 at 13:18

Tom · Accepted Answer · 2015-09-08 13:14:36Z

1

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
;

edited Sep 8, 2015 at 13:14

answered Sep 8, 2015 at 12:12

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accessing Macro variables using Proc sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related