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?
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;
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
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;
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.
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.
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 25. Read more here about why you should contribute and what is in it for you!
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.