SAS date in a pass-through query

Dates have to be passed into Teradata as quoted strings and you can't just concatenate single quotes to the macro variable using the native RDBMS syntax. What you need is the %BQUOTE function to mask a quote as you assign it to a macro variable. Here is an example where you want the last 5 days of data.

data _null_;
call symput( 'ystrdt', put(date() - 5, yymmdd10.) );
run;

%let q=%BQUOTE(');

PROC SQL;
CONNECT TO TERADATA (connection information);
EXEC(COMMIT) BY TERADATA;

EXEC
(
INSERT INTO db_name.test_table
SELECT ...some_list...
FROM db_name.some_table
WHERE some_date > &q&ystrdt&q
) BY TERADATA;
EXEC(COMMIT) BY TERADATA;

DISCONNECT FROM TERADATA;
QUIT;

Variables in Teradata

As of this writing, I am managing batch scripts that hit Teradata servers. Unlike MS SQL Server, Teradata does not allow functionality such as branching logic or variables. Unfortunately, this means the programmer has to bend over backwards to get work done and the code becomes less readable.

For this example, what would be a simple date variable is replaced with a volatile table containing a date. This allows dynamic filtering so that the programmer does not have to edit the script on a daily basis.

-- Create the temp table.
CREATE VOLATILE TABLE process_date (
batch_date DATE NOT NULL FORMAT 'yyyy-mm-dd'
)
UNIQUE PRIMARY INDEX (batch_date)
ON
COMMIT
PRESERVE ROWS;

-- Insert a value.
INSERT INTO process_date( batch_date )
SELECT DATE;


-- Query the previous 90 days of data using the date filter.
SELECT tbl.field_name
FROM database_name.table_name AS tbl
WHERE EXISTS (
SELECT *
FROM process_date AS PDT
WHERE tbl.date_name
BETWEEN PDT.batch_date - 90 AND PDT.batch_date - 1
);