How can I create custom macro variables using a macro and sas proc sql into?

Question

%macro get_names_into_macvar(name1=,into1=);
   proc sql;
   select name into :&into1 separated by ' '
        from column_names 
            where UPCASE(name) contains upcase("&name1");  
          ;
   quit;
%mend;
%get_names_into_macvar(name1=topic1, into1=topic1macvar);
%get_names_into_macvar(name1=topic2, into1=topic2macvar);

I have a very large data set with an insane amount of columns that follow a simple format. Each column represents a topic and a different metric for that topic. The column names look like topic1_metric1, topic1_metric2 ...., topic5_metric15, ... topic20_metric1

What I would like is to get a list of all column names for each given topic (or metric) and store that in a macro variable for future use. I already created the table of column names from the dictionary table. When I run the above sql code on its own, it works ... but copy and pasting and changing the topic names can't be the most efficient way to accomplish this.

proc sql;
   select name into :topic1macvar separated by ' '
        from column_names 
            where UPCASE(name) contains upcase("topic1");  
          ;
   quit;

My problem lies in creating custom macro variables to store it per topic.

select name into :&into1 separated by ' '

The above section of the code is not resolving into a macro variable. What am I doing wrong?

Is your problem that you are trying to use these macro variables after the macro has stopped running? Your current code has nothing to make the macro variables in the GLOBAL macro scope. So they will be local and disappear when the macro ends. To test add a %PUT to your macro to see if the values were actually stored. — Tom

Tom Tom · Accepted Answer · 2017-03-01T17:37:36

Most likely your trouble is that you are making local macro variables that disappear when the macro ends.

%macro get_names_into_macvar(name1=,into1=);
  %if not %symexist(&into1) %then %global &into1 ;
   proc sql noprint;
     select name into :&into1 separated by ' '
        from column_names 
        where UPCASE(name) contains %upcase("&name1")  
     ;
   quit;
%mend;

How can I create custom macro variables using a macro and sas proc sql into?

1 Answers