Search code examples
sqlsasdb2sas-macro

Macro's in PROC DB2EXT Queries?

Is it possible to put Macro variables in a PROC DB2EXT query?

This works:

WHERE SDD BETWEEN '01.05.2018' AND '30.05.2018'

But all my attempts of inserting a Macro variable all result in errors

Declaration of Macro

/* Statement in SAS, declaring the Macro, and transfering in to the remote session */
%let month =    05;
%SYSLPUT MONTH  =   &month;
%PUT &MONTH;

DB2EXT SQL

/* Error(-180): DSN00180E THE DATE, TIME, OR TIMESTAMP VALUE 01."&month.".2018 IS INVALID  */
WHERE SDD BETWEEN '01."&month.".2018' AND '30.05.2018'

/* Error(-206): DSN00206E 01.MAY.2018 IS NOT VALID IN THE CONTEXT WHERE IT IS USED */
WHERE SDD BETWEEN "01.&month..2018" AND '30.05.2018'

/* Futile attemps */
WHERE SDD BETWEEN '01&month2018' AND '30.05.2018'
WHERE SDD BETWEEN '01month2018' AND '30.05.2018'
Solution

  • Use the following construct When generating single quoted literals that need to contain an evaluated macro variable:

    %str(%')macro-evaluation%str(%')

    Ignore the slightly weird stack overflow (SO) colorizing of the above code. The SO renderer can't decipher the single quote as part of %str() closure

    So your case could be

    %str(%`)01&month.2018%str(%')