Search code examples
sqlsassas-macro

How to reference SAS variable with CATS in PROC SQL?

Very basic thing I can't seem to figure out. Columns in a particular table are numbers with underscores in front of them, I have a number stored in a macro variable and the name of the column I want to use is that number with an underscore. Say the number is 9, when I write the following code I get a column where each observation is _9 rather than column _9 from that dataset.

    proc sql;
    create table TM1 as
        select cats("_",&rat_as_num) from default_data;
    quit;
Solution

  • CATS returns a string, not a variable name. So the variable name will not matter, you get a string with _RAT_AS_NUM_VALUE.

    To return a variable name use %SYSFUNC() instead,

    data test;
_9 =  4;
run;


%let rat_as_num=9;

proc sql;
create table test2 as
select %sysfunc(cats(_, &rat_as_num)) from test;
quit;


proc print data=test2;
run;

    Per @Richards comments this also works:

    proc sql;
create table test2 as
select _&rat_as_nun from test;
quit;