PROC SQL works when written out but not in macro

[JacquesR]

Hi all

I have a (what I think to be) simple macro statement. I am trying to grab formats for some variables. The variable names are irrelevant, so I am not providing a dataset. Any dataset with two or three variables will do. Note that varlist is a space separated list of variable names in the dataset.

%macro MakeSummary(lib=WORK, dset=, varlist=, outfmt=SummaryFmt);

%LET MultRespVariablesList=%str(%')%sysfunc(tranwrd(%upcase(&varlist.),%str( ),%str(',')))%str(%');
%PUT &=MultRespVariablesList;
proc sql noprint;
        create table _varfmt as
        select name, format as fmtname
        from dictionary.columns
        where upcase(libname) eq upcase("&lib")
          and upcase(memname) eq upcase("&dset")
          and upcase(name) IN (&MultRespVariablesList.);  
    quit;
%mend;
%MakeSummary(lib=WORK, dset=test, varlist=Var1 Var2 Var3, outfmt=SummaryFmt);

This gives me:

ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
              a numeric constant, a datetime constant, a missing value, (, -, SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

However, if I turn on

options mprint;

And copy and past the output from the log, I get:

proc sql noprint;
create table _varfmt as select name, format as fmtname from
dictionary.columns where upcase(libname) eq upcase("WORK") and upcase(memname) eq
upcase("test") and upcase(name) IN
('VAR1','VAR2','VAR3');
quit;

And that runs fine.

I am getting the annoying:

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space between a quoted string and the succeeding identifier is recommended.

But I'm not certain that it relates here.

Why does the output of MPRINT, which should be what the macro processor is generating, working, but when I run the macro, it doesn't work?

[Ksharp · Accepted solution]

Try to use FINDW() instead of making a macro variable.

data test;
input id var1-var3;
format var3 f4.2;
cards;
1 1 2 3
;

%macro MakeSummary(lib=WORK, dset=, varlist=, outfmt=SummaryFmt);

proc sql noprint;
        create table _varfmt as
        select name, format as fmtname
        from dictionary.columns
        where upcase(libname) eq "%upcase(&lib)"
          and upcase(memname) eq "%upcase(&dset)"
          and findw("%upcase(&varlist.)", upcase(strip(name))) ;  
    quit;
%mend;
%MakeSummary(lib=WORK, dset=test, varlist=Var1 Var2 Var3, outfmt=SummaryFmt);

[JacquesR]

I'm still curious about why the MPRINT code works, but the macro doesn't?

[Ksharp]

If you really need to make your code worked, just add %UNQUOTE into it to get rid of delta character generated by %str().

data test;
input id var1-var3;
format var3 f4.2;
cards;
1 1 2 3
;

%macro MakeSummary(lib=WORK, dset=, varlist=, outfmt=SummaryFmt);

%LET MultRespVariablesList=%str(%')%sysfunc(tranwrd(%upcase(&varlist.),%str( ),%str(',')))%str(%');
%PUT &=MultRespVariablesList;
proc sql noprint;
        create table _varfmt as
        select name, format as fmtname
        from dictionary.columns
        where upcase(libname) eq upcase("&lib")
          and upcase(memname) eq upcase("&dset")
          and upcase(name) IN (%unquote(&MultRespVariablesList.));  
    quit;
%mend;
%MakeSummary(lib=WORK, dset=test, varlist=Var1 Var2 Var3, outfmt=SummaryFmt);

[FreelanceReinh]

Hi @JacquesR,

---

@Ksharp wrote:

If you really need to make your code worked, just add %UNQUOTE into it to get rid of delta character generated by %str().

... and use the %QSYSFUNC function instead of %SYSFUNC

%LET MultRespVariablesList=%str(%')%qsysfunc(tranwrd(%upcase(&varlist.),%str( ),%str(',')))%str(%');

to make the macro quoting consistent in the first place and to avoid the annoying notes in the log, especially "NOTE 49-169." Otherwise you produce a mixture of macro-quoted and unquoted single quotation marks, which can be seen in the output of a %put _user_; statement:

MAKESUMMARY MULTRESPVARIABLESLIST 
SEX','AGE','WEIGHT


[Ksharp]

FreelanceReinh ，
Good Point. Learned it happy.