DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 16

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

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.

);

Trusted Advisor
Posts: 1,399

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

[ Edited ]

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 

 

Occasional Contributor
Posts: 16

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

Thank you! That definitely helps me on my third point.
Occasional Contributor
Posts: 16

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

Bump
Ask a Question
Discussion stats
  • 3 replies
  • 131 views
  • 0 likes
  • 2 in conversation