topic Re: Teradata Passthrough Code for Date formats in SAS Programming

Teradata Passthrough Code for Date formats

crashz204 — Mon, 17 Jun 2019 19:57:58 GMT

In my code I am trying to use the passthrough to Teradata with SAS to automate a report. This automation i have defined dates.

in my Proc SQL using the Teradata passthrough i refer to the CW_START and CW_END dates for a weekly report. However this doesnt work in the passthrough even though I formatted the dates as YYMMDD10.

Not sure what I am doing incorrectly here so any pointers would help immensely. Thank you!

Data DATES;
Format CW_START
CW_END
REPORTDATE
CURR_REPT_DATE
YYMMDD10.;

REPORTDATE = today()-1;
CURR_REPT_DATE = today();
CW_START = intnx('Week.4',ReportDate,0,'B');
CW_END = intnx('Week.4',ReportDate,0,'E');

Call Symput('REPORTDATE',REPORTDATE);
Call Symput('CW_START',CW_START);
Call Symput('CW_END',CW_END);
Call Symput('CURR_REPT_DATE',CURR_REPT_DATE);

run;

Re: Teradata Passthrough Code for Date formats

ehmsoleil — Mon, 17 Jun 2019 22:53:48 GMT

Try removing the format before you use the call symput.

Re-format in your final product.

Dates in macro variables can be tricky!

Re: Teradata Passthrough Code for Date formats

Tom — Tue, 18 Jun 2019 01:18:47 GMT

To specify a date value in Teradata you should be able to use syntax like:

'2019-06-17'

Or be more precisely like

DATE'2019-06-17'

Notice that Teradata uses only single quote characters around literal values. Double quote characters are used around the names of objects (variables, tables, etc). But the SAS macro processor will not resolve macro variable references inside of single quotes.

So it is probably easiest to add the single quotes when you create the macro variable.

call symputx('CW_START',quote(put(CW_START,yymmdd10.),"'");

Then use the macro directly in your pass thru Teradata code.

where date_var <= &cw_start

Re: Teradata Passthrough Code for Date formats

crashz204 — Tue, 18 Jun 2019 17:43:22 GMT

Thank You, this worked great!