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.
Looks like DB2 wants hyphens between the fields. DB2 Basics: Fun with Dates and Times
%let mydate=%sysfunc(dequote("'%sysfunc(inputn(&mydate,YYMMDD8),YYMMDD10)'"));
Enclose your macro variables in double quotes. SAS doesn't resolve macro symbols in single quotes.
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
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
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)
Thanks, Tom. When I tried this I got a different error: "The date, time, or timestamp value 20150316 is invalid"
Looks like DB2 wants hyphens between the fields. DB2 Basics: Fun with Dates and Times
%let mydate=%sysfunc(dequote("'%sysfunc(inputn(&mydate,YYMMDD8),YYMMDD10)'"));
Tom you hit it! Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: