topic Re: How to use &SYSPARM date string yymmdd for PROC SQL DB2 select in SAS Programming

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

SharonBH — Mon, 16 Mar 2015 16:43:36 GMT

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.

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

Tim_SAS — Mon, 16 Mar 2015 17:00:03 GMT

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

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

slchen — Mon, 16 Mar 2015 17:52:11 GMT

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

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

Karthikeyan — Mon, 16 Mar 2015 18:11:54 GMT

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

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

Tom — Mon, 16 Mar 2015 20:08:27 GMT

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)

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

SharonBH — Tue, 17 Mar 2015 16:46:28 GMT

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

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

Tom — Tue, 17 Mar 2015 17:35:18 GMT

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

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

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

SharonBH — Tue, 17 Mar 2015 19:42:54 GMT

Tom you hit it! Thank you!