CODEWITHSUNDEEP
sassas-macro
JPOLIVA
JPOLIVA

Reputation: 65

SAS MACRO - concrenate SQL strings in macro

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

Upvotes: 0

Views: 96

Answers (2)

JPOLIVA
JPOLIVA

Reputation: 65

Ok, so i follow Tom comments and ended with a proc sql solution that works!

proc sql;
    select sql_tot_linhas,
        (case when sql_sum_num = '' then "0" else sql_sum_num end),
        (case when sql_min_data = '' then "." else sql_min_data end),
        (case when sql_max_data = '' then "." else sql_max_data end)
    into:sql_linhas, :sql_numeros, :sql_mindata, :sql_mxdata
    from libY.tableX 
    where livraria="&sel_livraria"
    and tabela="&sel_tabela";
quit;

proc sql;
     update libY.tableX 
     set tot_linhas = (&sql_linhas),
        sum_num =(&sql_numeros),
        min_data = (&sql_mindata),
        max_data = (&sql_mxdata)           
      where livraria="&sel_livraria"
      and tabela="&sel_tabela";
quit;

Tks Tom :)

Upvotes: 1

Tom
Tom

Reputation: 51566

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.

Upvotes: 1

Related Questions