DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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.


Accepted Solutions
Solution
‎03-17-2015 01:35 PM
Super User
Super User
Posts: 7,070

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

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

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

View solution in original post


All Replies
Super Contributor
Posts: 394

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

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

Super Contributor
Posts: 275

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

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

Contributor
Posts: 37

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

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

Super User
Super User
Posts: 7,070

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

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)

Occasional Contributor
Posts: 5

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

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

Solution
‎03-17-2015 01:35 PM
Super User
Super User
Posts: 7,070

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

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

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

Occasional Contributor
Posts: 5

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

Tom you hit it! Thank you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 667 views
  • 0 likes
  • 5 in conversation