How to use &SYSPARM date string yymmdd for PROC SQL DB2 select

[SharonBH]

I am trying to use a date string parsed from &SYSPARM in a MACRO that connects to DB2 with PROC SQL and selects records within a specific date range. How do I use string variable date in format yyyymmdd in PROC SQL to select records in a specific date range?

//SETVAR  SET CLCD='NY',INDATE='20150316'

//STEP1 EXEC SAS9,LOAD='db2loadinfo',PARM='SYSPARM="&CLCD&INDATE"'

%GLOBAL MYDATE;

%LET MYDATE=%SUBSTR(&SYSPARM,3,8);

.

%MACRO GetTRX(DBASE,TABLE,MYDATE);

PROC SQL NOPRINT;

CONNECT TO DB2(SSID=DBID1);

CREATE TABLE &TABLE AS

    SELECT * FROM CONNECTION TO DB2

         (SELECT *

               FROM &DBASE...&TABLE

                       WHERE (TRXDT BETWEEN DATE('&MYDATE') - 7 DAYS

                              AND DATE('&MYDATE'))

          );

DISCONNECT FROM DB2;

QUIT;

%MEND GetTRX

%GeTRX(DBNM1,TRANS,&MYDATE);

Received ERROR: (ACCDB2M013E) ERROR OPENING CURSOR.

[Tim_SAS]

Enclose your macro variables in double quotes. SAS doesn't resolve macro symbols in single quotes.

[slchen]

There is no date function in SAS; and first two lines of SAS codes were  hard to understand.

You could try something like this:

%GLOBAL MYDATE;

%LET MYDATE=%sysfunc(inputn(%sysfunc(putn(%substr(&sysparm,3,8),yymmdd8.)),date9.));

%MACRO GetTRX(DBASE,TABLE,MYDATE);

PROC SQL NOPRINT;

CONNECT TO DB2(SSID=DBID1);

CREATE TABLE &TABLE AS

    SELECT * FROM CONNECTION TO DB2

         (SELECT *

               FROM &DBASE...&TABLE

                       WHERE TRXDT BETWEEN %sysfunc(putn(%sysfunc(intnx(day,"&MYDATE"d,-7)),date9.))

                              AND &MYDATE

          );

DISCONNECT FROM DB2;

QUIT;

%MEND GetTRX

[Karthikeyan]

The Date function requires a valid DB2 date format, try using INDATE='2015-03-16' and you should remove the single quotes in the function call as you have specified it in the macro variable. It should look like WHERE (TRXDT BETWEEN DATE(&MYDATE) - 7 DAYS  AND DATE(&MYDATE) .

Thanks

Karthik

[Tom]

Looks like your JCL is setting SYSPARM to the value NY20150316 ?

Is that what you see inside SAS?

If so then your global macro variable MYDATE will be set to 20150316 (notice there are no quotes).

You then pass this into the local macro variable MYDATE.

You then try to pass this into DB2 by using the syntax '&MYDATE', but macro variables do not evaluate inside of single quotes.  So you literally passing '&MYDATE' into DB as the value for the DATE() function call.  I doubt that knows what to do with a string of characters that starts with an ampersand.

Here is one way to add the single quotes that I assume DB2 wants into the value of the macro variable:

%let mydate=%sysfunc(dequote("'&mydate'"));

You can then remove the single quotes in the code you are passing to DB2:

DATE(&MYDATE)

[SharonBH]

Thanks, Tom. When I tried this I got a different error: "The date, time, or timestamp value 20150316 is invalid"

[Tom · Accepted solution]

Looks like DB2 wants hyphens between the fields.  DB2 Basics:  Fun with Dates and Times

%let mydate=%sysfunc(dequote("'%sysfunc(inputn(&mydate,YYMMDD8),YYMMDD10)'"));

[SharonBH]

Tom you hit it! Thank you!