BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dburton
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
OS2Rules
Obsidian | Level 7

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;

View solution in original post

4 REPLIES 4
KarlK
Fluorite | Level 6

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
Obsidian | Level 7

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
Calcite | Level 5

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
Calcite | Level 5

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6218 views
  • 4 likes
  • 3 in conversation