Error in Macro Function

Question

I'm using SAS Enterprise Guide. New to writing SAS macro functions. Nested a proc sql inside a macro. I'm trying to first check if a column exists and return the column number and then using the column number, to get the column name so that I can call this macro function in a query builder. However I'm getting ERROR 180-322 under the 'select cats' part of the code. Listed below is the code I have written so far:

%macro varexist(ds,var);
%local rc dsid result dynvar;
%let dsid = %sysfunc(open(&ds));
%let result = %sysfunc(varnum(&dsid,&var));
%let rc =%sysfunc(close(&dsid));
proc sql;
    select cats('t1.',name) into :dynvar separated by ', '
    from dictionary.columns 
    where libname = 'WORK' and
          memname = 'TRANSPOSE_DATA' and 
           varnum = "&result";
quit;
&dynvar
%mend varexist;

%put %varexist(WORK.TRANSPOSE_DATA,VAR1);

While I couldn't fix the issue, I manged to change the logic and get the new code to work. I'm leaving this question unanswered, in case someone can post the solution — Jayesh Surendran Menon, Feb 11 '16 at 01:30
Your program doesn't make much sense as coded. If you got it to work post the fixed program as the answer. Perhaps with an explanation of what it is doing. — Tom, Feb 11 '16 at 02:05
%macro varexist(ta,ds,var); %local rc dsid result col_name; %let dsid = %sysfunc(open(&ds)); %let result = %sysfunc(varnum(&dsid,&var)); %let rc =%sysfunc(close(&dsid)); %if &result > 0 %then %let col_name = &ta&var; %else %if &ta = t1. %then %let col_name = ''; &col_name %mend varexist; — Jayesh Surendran Menon, Feb 11 '16 at 02:16

score 3 · Accepted Answer · answered Feb 11 '16 at 02:10

You have not coded a "MACRO FUNCTION", since the macro emits multiple statements (proc sql; select ... quit;). So if you tried to use it as if it was a function like this:

%let myvar=%varexist(work.transpose_data,age);

then you will end up generating code like:

%let myvar=proc sql;
select cats('t1.',name) ... ;
quit;

So the reason the the select... generates an error is because it is not within a PROC SQL step as the PROC SQL statement has become the value assigned by the %LET statement.

score 0 · Answer 2 · answered Mar 07 '16 at 23:43

As mentioned in one our my comments, I changed the logic and got it work as follows:

%macro varexist(ta,ds,var); 
%local rc dsid result col_name; 
%let dsid = %sysfunc(open(&ds)); 
%let result = %sysfunc(varnum(&dsid,&var)); 
%let rc =%sysfunc(close(&dsid)); 
    %if &result > 0 %then %let col_name = &ta&var; 
    %else %if &ta = t1. %then %let col_name = ''; 
&col_name 
%mend varexist;

Error in Macro Function

2 Answers2