topic Using macro variables for date timestamp sql pass-thru to DB2 in SAS Procedures

Using macro variables for date timestamp sql pass-thru to DB2

dburton — Tue, 27 Mar 2012 16:03:09 GMT

Fairly new to SAS and trying to rewrite a messy, manual process code block and I have run into a problem that I have not been able to resolve.

I read a data set in from a csv file that contains 2 variables representing a table name and a corresponding timestamp.

So for example, record 1 would be:

OECUSEML 2012-03-22-10.49.18.283000

I then run the following code to create global macro variables for each record.

data _null_;

set Timestamps;

call symput (Table, DatetimeStamp);

run;

This creates a global macro variable called &OECUSEML with the above timestamp value.

I then am trying to reference this variable in the following code that connects to a DB2 database:

create table OECUSEML

as select *

from connection to odbc (

select CUSNBER, ADDTYPCLF, EMLADR, SEQNBR,

int(replace(char(DATE(DATADD), ISO),'-','')) as DATADD,

EMLSRCCLF, LSTUPDJOB, LSTUPDPGM, LSTUPDUSR, LSTUPDNBR,

int(replace(char(DATE(LSTUPDTSP), ISO),'-','')) as LSTUP_DATE, TIME(LSTUPDTSP) as LSTUP_TIME,

LSTUPDTSP

from PRDOTCDTA.OECUSEML

where LSTUPDTSP > &OECUSEML);

quit;

I get a SQL0104 - Token .18 was not valid error. The variable is resolving to the correct value, but the where statement doesn't have single quotes around the value.

If I substitute the &OECUSEML variable with the actual '2012-03-22-10.49.18.283000' value including single quotes, it runs with the expected results.

If I change the where clause, I get the following errors:

where LSTUPDTSP > '&OECUSEML'); -- SQL0180 - Syntax of timestamp value not valid.

where LSTUPDTSP > "&OECUSEML"); -- SQL0206 - Column "2012-03-22-10.49.18.283000" not in specified tables.

I need the where clause to pull the timestamp down to the milliseconds so I don't miss a records. The purpose is to create a dynamic extract that can manage its own incrementals. I have spent a lot of time pouring over the internet to try to find a solution to this problem.

Any suggestions?

Using macro variables for date timestamp sql pass-thru to DB2

KarlK — Tue, 27 Mar 2012 17:12:55 GMT

I don't have DB2, but, in reading your description of what you've tried, DB2 wants the datetime to have single quotes around it, and double won't work. If you put single quotes around your macro variable, SAS won't resolve it. If you put double quotes, it resolves, but DB2 complains. Catch-22.

Here's one way I can think of to single-quote a macro variable and still have it resolve. In between your datastep with the symput and the Proc SQL, insert the following statement:

%let oecuseml = %str(%')&oecuseml%str(%');

The contents of your macro variable are now: '2012-03-22-10.49.18/283000'

See if that works.

Karl

Using macro variables for date timestamp sql pass-thru to DB2

OS2Rules — Tue, 27 Mar 2012 17:28:06 GMT

Hi:

We've been using DB2 (z/OS mainframe) for years like this....

DATA _NULL_;

Set somedata;

DB2_DATE=EXTRACT_DATE;

CALL SYMPUT('DB2_DATE',"'"||PUT(DB2_DATE,YYMMDDD10.)||"'");

RUN;

PROC SQL FEEDBACK ;

CREATE TABLE mytabel as

SELECT *

FROM CONNECTION TO DB2

(SELECT a bunch of fields

FROM DB2.table

WHERE ACCOUNT IN ( &LIST ) AND

A.DATE >= &DB2_DATE

);

quit;

run;

Using macro variables for date timestamp sql pass-thru to DB2

dburton — Tue, 27 Mar 2012 17:47:50 GMT

This solution seems logical to me, but I had not run across the format YYMMDDD10. before. When I run this, I get an ERROR 48-59: The format $YYMMDDD was not found or could not be loaded.

Using macro variables for date timestamp sql pass-thru to DB2

dburton — Tue, 27 Mar 2012 18:03:43 GMT

SUCCESS!!!!

Using you logic, but simplifying it a little more I did this and it resolves correctly.

call symput (Table, "'"||DatetimeStamp||"'");

Thank you both for your responses.