topic Re: How do I create date prompt in SAS EG, calculate other dates from that date, & pass to Terad in SAS Programming

How do I create date prompt in SAS EG, calculate other dates from that date, & pass to Teradata?

robertyknwt — Tue, 24 Jan 2017 17:46:44 GMT

I've got a bunch of queries which use a few different dates in different WHERE clauses in PROC SQL statements. Some of those queries are pass-through queries to Teradata. I've gone through documentation and other questions/answers here, and tried different things to figure this out, but I haven't found the solution that works. This is what I want to do:

Prompt the user for a month and year (e.g. OCT16, like MONYY. format);
Calculate various dates from that prompt result (e.g. calculate 2016-10-31 and 2015-11-01 as &EndDate and &StartDate from OCT16);
Format those dates properly in a PROC SQL statement that is a pass-through query to one of our Teradata data warehouses, as in the following:

connect to teradata as mycon(server= dwprod3 authdomain=teradataauth mode=teradata );
create table MyTable as
select * from connection to mycon
(
Select *

From COC_V20_NOHAPHI.TABLE

WHERE TABLE.RecordDate between &StartDate. and &EndDate.

);

Re: How do I create date prompt in SAS EG, calculate other dates from that date, & pass to Terad

Shmuel — Tue, 24 Jan 2017 19:02:22 GMT

I have searched google for "terradata date litereal" and found next link,

which I believe you'll find there the syntax:

http://teradataforum.com/l070316a.htm

example taken from there:

Literals

Date literals can be specified in many ways:

-- in the same format as the column they are being compared to

where infamy_date = '12/07/1941'
-- with an explicit format

where infamy_date = '07Dec1941' (date, format 'DDMMMYYYY')
-- in ANSI standard form (must be preceded by the keyword DATE)

where infamy_date = date '1941-12-07'
-- in numeric form (not recommended)

where infamy_date = 411207

Re: How do I create date prompt in SAS EG, calculate other dates from that date, & pass to Terad

robertyknwt — Tue, 24 Jan 2017 19:18:59 GMT

Thank you! That definitely helps me on my third point.

Re: How do I create date prompt in SAS EG, calculate other dates from that date, & pass to Terad

robertyknwt — Wed, 25 Jan 2017 18:24:15 GMT

Bump