CODEWITHSUNDEEP
sqlmacrossas
Chris
Chris

Reputation: 159

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

%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?

Upvotes: 0

Views: 90

Answers (1)

Tom
Tom

Reputation: 51621

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;

Upvotes: 2

Related Questions