topic Re: SAS Query on macro and Proc SQL in SAS Programming

SAS Query on macro and Proc SQL

sarthakkataria — Tue, 13 Mar 2018 05:38:34 GMT

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!

Re: SAS Query on macro and Proc SQL

Shmuel — Tue, 13 Mar 2018 06:17:09 GMT

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;

Re: SAS Query on macro and Proc SQL

sarthakkataria — Tue, 13 Mar 2018 07:03:31 GMT

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!

Re: SAS Query on macro and Proc SQL

Shmuel — Tue, 13 Mar 2018 07:15:35 GMT

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

Re: SAS Query on macro and Proc SQL

LinusH — Tue, 13 Mar 2018 15:52:46 GMT

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.