DATA Step, Macro, Functions and more

insert date macro variable into teradata

Reply
Frequent Contributor
Posts: 110

insert date macro variable into teradata

[ Edited ]

I have a column defined as follows in a TeraData table:

 

 

RunDay DATE FORMAT 'YYYY-MM-DD'

 

 

I try to insert one row into this table as follows given a macro variable RunDay:

 

 

 

%let RunDay = %sysfunc(datetime());

proc sql noerrorstop;
	connect to teradata(User=&user. password=&passwd. mode=teradata);
	Execute(
		INSERT INTO SomeGreatTable
		(
			RunDay
		)
		VALUES 
		(
			input("&RunDay.",YYMMDD.)
		);
	);
)By Teradata;
quit;

Any ideas why this does not work? Thanks!

 

 

 

Super User
Super User
Posts: 7,977

Re: insert date macro variable into teradata

Posted in reply to csetzkorn

What does not work?

 

Look at what you are doing:

%let RunDay = = %sysfunc(datetime());
input("&RunDay.",YYMMDD.)

 

Now de-refeference the macro variable:

input(" = %sysfunc(datetime())",YYMMDD.)

 

Does that look correct to you?  No, even if you fix the double = sign, the %sysfunc() will return a number - which is how SAS stores dates/times/datetimes.  This is basic SAS stuff which you should know before trying to do macros.

Something like:

%let RunDay=%sysfunc(datetime());

put(datepart("&RunDay."),YYMMDD.)

 

Should work, however not tested.

 

Super User
Posts: 7,832

Re: insert date macro variable into teradata

Slight modification:

put(datepart(&RunDay.),YYMMDD.)

as &RunDay will be (and should be) a numeric value.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 110

Re: insert date macro variable into teradata

Thanks but it does not: ERROR: Teradata execute: Syntax error: Data Type "datepart" does not match a Defined Type name.
Super User
Super User
Posts: 7,065

Re: insert date macro variable into teradata

[ Edited ]
Posted in reply to csetzkorn

If you are pushing SQL code into Teradata to execute it needs to be valid Teradata SQL code. That means you can't use the INPUT() function.  Even if you could your macro variable was not formatted in the form that your INPUT statement was trying to read.  Try making your macro variable have the content '2017-02-14' instead. Note to get a DATE value you want to use the DATE() function and not the DATETIME() function.

 

%let RunDay = %unquote(%bquote('%sysfunc(date(),yymmddd10)'));

proc sql noerrorstop;
	connect to teradata(User=&user. password=&passwd. mode=teradata);
	Execute(
		INSERT INTO SomeGreatTable
		(
			RunDay
		)
		VALUES 
		(
			&RunDay 
		);
	);
)By Teradata;
quit;

 

Frequent Contributor
Posts: 110

Re: insert date macro variable into teradata

Thanks Tom. I am getting: ERROR: Teradata execute: A character string failed conversion to a numeric value.
Super User
Super User
Posts: 7,065

Re: insert date macro variable into teradata

Posted in reply to csetzkorn

Probably because the macro variable was generated using the DATETIME() function instead of the DATE() function. That would probably cause the YYMMDDD10. format to generate *********************.  Let me update my answer above to correct that.

Frequent Contributor
Posts: 110

Re: insert date macro variable into teradata

It appears that this:

 

%let RunDay = 2016-10-01;

 

and this works:

 

CAST(%bquote('&RunDay.') AS DATE FORMAT 'YYYY-MM-DD')

 

Last hurdle is to inialise the macro variable with the current run day (rather than hardcoding it).

Trusted Advisor
Posts: 1,575

Re: insert date macro variable into teradata

[ Edited ]

@Tom,  you probably meant: 

 

%let RunDay = %unquote(%bquote('%sysfunc(today(),yymmddd10)'));

 or maybe you need take of the quotes:

 

%let RunDay = %unquote(%bquote(%sysfunc(today(),yymmddd10)));

 

Super User
Super User
Posts: 7,065

Re: insert date macro variable into teradata

Yes. The original request used DATETIME() instead of DATE().  I updated my post to fix that.

Note the TODAY() is just an alias for the DATE() function.

Ask a Question
Discussion stats
  • 9 replies
  • 324 views
  • 0 likes
  • 5 in conversation