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

[dburton]

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?

[KarlK]

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

[OS2Rules · Accepted solution]

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;

[dburton]

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.

[dburton]

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.