Reference of macro variables for dynamic sql query in SAS

Question

　　I used SAS macro variable to store the 2 sql queries (2 macros for them) for dynamic execution. The difference is the one is the query string for SAS SQL and the other one is the query string for MS SQL which will be executed with the pass-through facility.The 2 queries are composed very similarly. I utilized %STR and %NRSTR to form the query strings and made them stored into the macro variables. The following is the whole part of the written macro function:

%LET initialDate=19Oct2016;
%LET lastDate=19Oct2016;
%MACRO compressDataSetFirstTime(table, library=ANY);
PROC SQL NOPRINT;
    /*Get the character variables*/
    SELECT name, varnum INTO :characterVariableList SEPARATED BY ', ' 
    　FROM DICTIONARY.COLUMNS
    　WHERE libname="&library" AND memname="&table" AND type='char'
    　ORDER BY varnum;
    /*Get the numeric variables*/
    SELECT name, varnum, type INTO :numericVariableList SEPARATED BY ', ' 
    　FROM DICTIONARY.COLUMNS
    　WHERE libname="&library" AND memname="&table" AND type='num'
    　ORDER BY varnum;

%PUT 文字變數清單：&characterVariableList;
%LET characterVariableList=(&characterVariableList);
%LET numericVariableList=%STR(&numericVariableList, );
%PUT 數值變數清單：&numericVariableList;
%LET queryString=%STR(SELECT ); %LET macroClause=%STR( INTO ); 
%LET queryMSSQL=%STR(SELECT )&numericVariableList;
%LET listNumber=%SYSFUNC(COMPRESS(%EVAL(%SYSFUNC(COUNT(&characterVariableList, %STR(,)))+1)));
%PUT 共有&listNumber.個文字型態變數。;
/*Compose the query for macro and the mssql query.*/
%DO variableNumber=1 %TO &listNumber;
    %IF &variableNumber LT &listNumber %THEN %DO;
        %LET variableName=MAX(LENGTH(STRIP(%SCAN(&characterVariableList, &variableNumber)))),;
        %LET queryString=&queryString&variableName;
        %LET macroClause=&macroClause%STR(:variable&variableNumber, );
        %LET variableNameMSSQL=SUBSTRING(%SCAN(&characterVariableList, &variableNumber), 1, %NRSTR(%SYSFUNC%(COMPRESS%(&)variable&variableNumber))) AS %SCAN(&characterVariableList, &variableNumber),%STR( );
        %LET queryMSSQL=&queryMSSQL&variableNameMSSQL;
    %END;
    %ELSE %DO;
        %LET variableName=MAX(LENGTH(STRIP(%SCAN(&characterVariableList, &variableNumber))));
        %LET queryString=&queryString&variableName;
        %LET macroClause=&macroClause%STR(:variable&variableNumber);
        %LET variableNameMSSQL=SUBSTRING(%SCAN(&characterVariableList, &variableNumber), 1, %NRSTR(%SYSFUNC%(COMPRESS%(&)variable&variableNumber))) AS %SCAN(&characterVariableList, &variableNumber);
        %LET queryMSSQL=&queryMSSQL&variableNameMSSQL;
    %END;
%END;
%LET queryString = &queryString&macroClause%STR( FROM &library..&table WHERE dates BETWEEN "&initialDate"D AND "&lastDate"D;);
%LET queryMSSQL = &queryMSSQL%STR( FROM dbo.&table WHERE dates BETWEEN %NRSTR("%SYSFUNC(PUTN("&initialDate"D, YYMMDD10.))") AND %NRSTR("%SYSFUNC(PUTN("&lastDate"D, YYMMDD10.))"););
%PUT The query string with macro clause：&queryString;
/*Execute the query for macro*/
    &queryString
/*
    This is for examination.
%DO checkNumber=1 %TO &listNumber;
    %PUT variable&checkNumber.接受到的長度：%SYSFUNC(TRIM(&&variable&checkNumber));
%END;*/
%PUT 遞過功能MSSQL查詢語句：&queryMSSQL;
/*Execute the mssql query with the pass-through facility*/
    CONNECT TO SQLSVR AS sjconn
        (DATABASE="***" USER=*** PASSWORD=***);
        CREATE TABLE Desirable_Result AS
        SELECT *
        FROM connection to sjconn
        ( 
            /* The printed result of queryMSSQL from log using %PUT. */
            /* &queryMSSQL */
        );
        DISCONNECT FROM sjconn;
QUIT;
%MEND;

The problem I met is the query stored in the macro variable cannot be executed in the PROC SQL with the pass-through facility.

　　The second query is composed of the SUBSTRING function in MSSQL, %SYSFUNC, and some macro variables, which appears like the following, with %PUT:

SELECT SUBSTRING(var, 1, %SYSFUNC(COMPRESS(&len_var))) AS var
FROM table 
WHERE date BETWEEN "%SYSFUNC(PUTN("&firstdate"D, YYMMDD10.))" AND 
 "%SYSFUNC(PUTN("&lastdate"D, YYMMDD10.))";

　　Assume &query stores the query string, my problem is if I used the printed result in log and put it in PROC SQL with the pass-through facility, it worked, yet if I used &query and put it in PROC SQL with the pass-through facility, it failed.

　　In a nutshell,

This works

CONNECT TO SQLSVR AS conn (related setting);
CREATE TABLE TMP AS
SELECT *
FROM connection to conn
(
     /* The following query is the result I coped from the log where I used %PUT to print <br> the content of &query */
　　　SELECT SUBSTRING(var, 1, %SYSFUNC( COMPRESS(&len_var))) AS var
　　　FROM table 
　　　WHERE date BETWEEN "%SYSFUNC(PUTN("&firstdate"D, YYMMDD10.))" AND 　　　"%SYSFUNC(PUTN("&lastdate"D, YYMMDD10.))";
);

This fails

CONNECT TO SQLSVR AS conn (*related setting*);
CREATE TABLE TMP AS 
SELECT * 
FROM connection to conn
(
     &query
);

The error message in log displays that the syntax near 'SYSFUNC' is incorrect.

Any clue why that is?

Answer 1

You can't use %SYSFUNC that way, unfortunately. %SYSFUNC (which needs a % if you didn't leave it off just due to typo) has to execute a system function; you're not asking it to to that here. Your "this works" does not, in fact, work, or at least shouldn't, and probably doesn't do what you're expecting it to do if it does actually return something (that would surprise me immensely).

For example, this simplest-of-things:

%let x = %SYSFUNC("&firstdate"D, YYMMDD10.);

Fails with an error:

ERROR: Function name missing in %SYSFUNC or %QSYSFUNC macro function reference.

So, how do we get around that? Fairly easy, use a simple function putn. In fact you can just wrap it around what you have now and it will directly work.

%let firstdate=01JAN2015;
%let x = %SYSFUNC(putn("&firstdate"D, YYMMDD10.));
%put &=x;

---

As to why it appears to work to you if you run it the first way and not the second... who knows. If it actually does work the first way (if you have inaccurately transcribed the example), then my suggestion would be that you have done something with macro quoting that the SQL compiler isn't willing to un-do. %unquote when you use it is not a bad idea as a first step to figuring it out, or switch up how you quote it (use %str instead of %nrstr or use %bquote instead of %str etc.) to see what works and what doesn't; PROC SQL is notoriously finicky when it comes to macro quoting, and things that the data step will happily automatically unquote SQL won't always.