Error in Macro Function

0
votes

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);
2
sassas-macro
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
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
%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

2 Answers

3
votes

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.

0
votes

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;