SAS Query on macro and Proc SQL

Reply
New Contributor
Posts: 2

SAS Query on macro and Proc SQL

Hi,

I wanted to know how can we check whether a column is present in a table or not using macro? 

And i want to update that table if it is present in the table. 

So I will have to use Proc SQL as well.

 

Thanks in advance, waiting for a quick reply!

 

 

Trusted Advisor
Posts: 1,837

Re: SAS Query on macro and Proc SQL

[ Edited ]
Posted in reply to sarthakkataria

You can use next code in a macro program to check does variable exist in a table:


%let dsid=%sysfunc(open(table_in,i));
%let var_num=%sysfunc(varnum(&dsid,VARIABLE_NAME));
%if %eval(&VAR_num) = 0 %then %do;
     <variable does not exit>
%end;
%else do;
    <variable exists>
%end;

you can even check it in a datastep:

data table_out;
  set table_in;
       dsid   =  open(table_in,i);
       var_num = varnum(dsid,'<variable name>');
       if var_num > 0 then do;
          <variable exists>
        end;
    ........
run;
New Contributor
Posts: 2

Re: SAS Query on macro and Proc SQL

Hi,

Thanks for the code, but when I'm using Proc SQL fro updating the table, I am getting some errors.

 

I am using the following code, where employee is the name of my table and Emp_ID is one of the variable.

 

%let dsid=%sysfunc(open(employee)); 
%let var_num=%sysfunc(varnum(&dsid,Emp_Add));
proc sql;
%if %eval(&var_num) >0 %then %do;
update employee as a
set Emp_ID = Emp_ID * 100;
end;
%else %do; 
select account_number from Employee_Salary
%end;
%quit;

 

It says %IF, %else are not valid in open code!

Trusted Advisor
Posts: 1,837

Re: SAS Query on macro and Proc SQL

Posted in reply to sarthakkataria

You cannot use macro statements, like %IF, in an open code - neither in a data step nor in a sql code.

You have to enclose it in a macro program:

 

%macro macro_name(<any arguments>);

     ....

     proc sql;

           .........

           %if <any condition> %then %do;

                 .......

            %end;

           .......

      quit;

%mend;

%macro_name(<supply arguments>);

Super User
Posts: 5,881

Re: SAS Query on macro and Proc SQL

Posted in reply to sarthakkataria
Actually, from 9.4 M5 (or 4 documentation is not clear) you can use %IF in open code...but it seems that the OP is on an earlier release.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 119 views
  • 1 like
  • 3 in conversation