SAS Executing empty SQL commands in macro

Question

Ive a table with some SQL commands that i execute in a macro, but when they empty i get some syntax errors because its expected some value. My table looks like this:

libname	tablename	SQL MAX_DATE	SQL_SUM_NUM
lib1	table1	select max(date) from lib1.table1	select sum(ammount) from lib1.table1
lib1	table2	select max(dt_a) from lib1.table2
lib2	table1		select sum(cash) from lib2.table1

Now. i've a macro that run all records in this table and updates the result of the sql commands to other fields (sum_num, max_date), something like:

%macro exec_SQL;
proc sql;
  select libname, tablename, sql_max_date, sql_sum_num
  into :lib1-, :tab1-, :sql_dt1-, :sql_num1-
  from have
quit;
 
%do i=1 %to &sqlobs;
  proc sql;
    update have 
    set 
      max_date=(&&sql_dt&i.), 
      sum_num=(&&sql_num&i.)
    where libname="&&lib&i."
    and tablename="&&tab&i.";
  quit;
%end;
%mend exec_SQL

%exec_SQL

Any way to get around this? I tryed to use the case statment on my update, but didnt have any luck, getting also another errors of syntax :(

Answer 1

Refactor the first PROC SQL to get each update SQL separately, with the variable to be updated embedded in the macro variable.

%macro exec_SQL;
proc sql;
  select libname, tablename, upd
  into :lib1-, :tab1-, :upd1-
  from (
    select libname, tablename, cats('max_date=(',sql_max_date,')') as upd
    from have 
    where not missing(sql_max_date)
    union
    select libname, tablename, cats('sum_num=(',sql_sum_num,')') as upd
    from have 
    where not missing(sql_sum_num)
  );
quit;
 
%do i=1 %to &sqlobs;
  proc sql;
    update have 
    set &&UPD&I
    where libname="&&lib&i."
      and tablename="&&tab&i.";
  quit;
%end;
%mend exec_SQL;

%exec_SQL;