SAS Query on macro and Proc SQL

[sarthakkataria]

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!

[Shmuel]

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;

[sarthakkataria]

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!

[Shmuel]

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>);

[LinusH]

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.