SAS MACRO - concrenate SQL strings in macro

Question

I have a libY.tableX that have for each record some SQL strings like the ones below and other fields to write the result of their execution.

select count(*) from libZ.tableK
select sum(fieldV) from libZ.tableK
select min(dsitact) from libZ.tableK

This my steps:

1. the user is prompted to select a lib and table and the value is passed to the vars &sel_livraria and &sel_tabela;
2. My 1st block is a proc sql to get all the sql string from that record.
3. My 2nd block is trying to concrenate all that strings to use further on to update my table with the results. The macro %isBlank is the one recommended by Chang CHung and John King in their sas papper;
4. My 3th block is to execute that concrenated sql string and update the table with results.

%macro exec_strings;

  proc sql noprint ;
    select livraria, tabela, sql_tot_linhas, sql_sum_num, sql_min_data, sql_max_data
    into :livraria, :tabela, :sql_tot_linhas, :sql_sum_num, :sql_min_data, :sql_max_data
    from libY.tableX    
    where livraria='&sel_livraria'
    and tabela='&sel_tabela';
  quit;

  %LET mystring1 =%str(tot_linhas=(&sql_tot_linhas));
  %LET separador =%str(,);
  %if %isBlank(&sql_sum_num) %then %LET mystring2=&mystring1;
    %else %LET mystring2= %sysfunc(catx(&separador,&mystring1,%str(sum_num=(&sql_tot_linhas))));
  %if %isBlank(&sql_min_data) %then %LET mystring3=&mystring2 ;
    %else %LET mystring3= %sysfunc(catx(&separador,&mystring2,%str(min_data=(&sql_min_data))));
  %if %isBlank(&sql_max_data) %then %LET mystring0=&mystring3;
    %else %LET mystring0= %sysfunc(catx(&separador,&mystring3,%str(max_data=(&sql_min_data))));
  %PUT &mystring0;

  proc sql noprint;
    update libY.tableX
    set &mystring0
    where livraria='&sel_livraria'
    and tabela='&sel_tabela';
  quit;

%mend;

My problem with the code above is that iam getting this error in my final concrenated string, &mystring0.

tot_linhas=(&sql_tot_linhas),sum_num=(&sql_tot_linhas),min_data=(&sql_min_data),max_data=(&sql_min_data)
            _                         _                          _                        _                                            
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,  a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. 

Any help appreciated

Answer 1

It is very hard to tell from your description what it is you are trying to do, but there are some clear coding issues in the snippets of code you did share.

First is that macro expressions are not evaluated in string literals bounded by single quotes. You must use double quotes.

where livraria="&sel_livraria"

Second is you do not want to use any of the CAT...() SAS functions in macro code. Mainly because you don't need them. If you want to concatenate values in macro code just type them next to each other. But also because they do not work well with %SYSFUNC() because they allow their arguments to be either numeric or character so %SYSFUNC() will have to guess from the strings you pass it whether it should tell the SAS function those strings are numeric or character values.

So perhaps something like:

%let mystring=tot_linhas=(&sql_tot_linhas);
%if not %isBlank(&sql_sum_num) %then 
  %LET mystring=&mystring,sum_num=(&sql_tot_linhas)
;
%if not %isBlank(&sql_min_data) %then 
  %LET mystring=&mystring,min_data=(&sql_min_data)
;
%if not %isBlank(&sql_max_data) %then
  %LET mystring=&mystring,max_data=(&sql_max_data)
;

Note that I also cleaned up some obvious errors when modifying that code. Like the extra & in the value passed to the %ISBLANK() macro and the assignment of the min value to the max variable.

But it would probably be easier to generate the strings in a data step where you can test the values of the actual variables and if needed actually use the CATX() function.