Help needed for date formatting in macro!

[ballardw]

Looks like the condition in SQL might call for a NOT BETWEEN:

sql-expression <NOT> BETWEEN sql-expression AND sql-expression

[stefflovessas]

Thanks everyone, but unfortunately this is still giving me issues. It's just not understanding date formats in this macro, no matter which way i try to express them. when they're in date9, it gives me this error. is anyone familiar with the 'numeric literal'?

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0103N The numeric literal "01JUL2009" is not valid. SQLSTATE=42604

[Tom]

Look at this SAS publication. http://support.sas.com/techsup/technote/ts566a.pdf

It looks like DB2 wants datetime constants in the format 'YYYYMMDDHHMMSS'.  It probably also wants the single quote characters rather than double quotes.

[FloydNevseta]

I think you have one and/or two problems.

1. The date format ddmmmyyyy may not be interpreted as a valid date string in db2. You should try yymmdd10. which is recognized by many dbms'.

2. In SQL pass-through, you need to make sure that the sql statements formed by any resolved macros are in the form that is recognized by the dbms. For example, I often pass date values in macros to a Teradata database. I first format the dates as yymmdd10.. Then I have to enclose the date value in single quotes. So most Teradata sql statements involving dates are written like this in my SAS code: where start_date = %str(%')&dateval%str(%'). When the SAS code runs, the masked single quotes allow the macro dateval to resolve, but the resulting date parameter will have single quotes that get passed to the Teradata sql processor which is expecting single quotes around data strings. I haven't worked with db2 in many years, so I cannot recall if date strings require singel quotes.

Hope that helps.

[Patrick]

I would suggest you send us some plain PROC SQL code which you've tested. Eg. something like below:

proc sql;

  create view work.NB_ATT_plcytrmTEST as

  select distinct * from connection to db2 (

    select *

    from edw.table_a

    where effective_dt GE <hard coded date value in DB2 syntax>

      and effective_dt LT <hard coded data value DB2 syntax>

    ;

  );

quit;

And then you tell us exactly what you want to achieve - eg. create yearly SAS tables based on a start date and a number of years (you see I don't fully understand what you're after based on the code you've posted).

Looking at the initial macro code you've posted, I feel there could be an easier approach (eg: one query to the data base creating a view and then a SAS data step executing this view and splitting up the data into multiple SAS tables).

[Patrick]

I've run below code against DB2 using implicit SQL.

OPTIONS sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql;
select start_dt, end_dt
from DB2Lib.rpp_plan
where start_dt='13sep2008'd
;
quit;

That's what you see in the log:

DB2_14: Prepared: on connection 2
SELECT  "START_DT", "END_DT"  FROM DI_DM.RPP_PLAN  WHERE  ( "START_DT" = DATE({d '2008-09-13' }) )
FOR READ ONLY

DB2: COMMIT performed on connection 2.

DB2_15: Executed: on connection 2
Prepared statement DB2_14

And this shows you exactly how for explicit pass-through DB2 SQL a date string must be passed:   DATE({d '2008-09-13' })

[JonS_]

Hi ya, here is an example of How I format the date...

data _null_;

dt = today();

startdate = (intnx('month',dt,-1));

enddate = intnx('month',dt,0)-1;

call symput ('startdate',put(startdate,mmddyy10.));

call symput ('enddate',put(enddate,mmddyy10.));

format startdate enddate mmddyy10.;

run;

%let startdt = %BQUOTE(')&startdate%BQUOTE(');

%let Enddt= %BQUOTE(')&enddate%BQUOTE(');

proc sql;

create table steff.NB_ATT_plcytrm as select distinct * from connection to db2(

select * from edw.table_a

where effective_dt >= date(&startdt.)

and effective_dt < date(&enddt.)

);

quit;