topic Re: insert date macro variable into teradata in SAS Programming

insert date macro variable into teradata

csetzkorn — Tue, 14 Feb 2017 15:50:47 GMT

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!

Re: insert date macro variable into teradata

RW9 — Tue, 14 Feb 2017 15:22:28 GMT

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.

Re: insert date macro variable into teradata

Kurt_Bremser — Tue, 14 Feb 2017 15:34:22 GMT

Slight modification:

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

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

Re: insert date macro variable into teradata

csetzkorn — Tue, 14 Feb 2017 15:35:55 GMT

Thanks but it does not: ERROR: Teradata execute: Syntax error: Data Type "datepart" does not match a Defined Type name.

Re: insert date macro variable into teradata

Tom — Tue, 14 Feb 2017 16:16:40 GMT

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;

Re: insert date macro variable into teradata

csetzkorn — Tue, 14 Feb 2017 15:55:19 GMT

Thanks Tom. I am getting: ERROR: Teradata execute: A character string failed conversion to a numeric value.

Re: insert date macro variable into teradata

Tom — Tue, 14 Feb 2017 16:15:19 GMT

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.

Re: insert date macro variable into teradata

csetzkorn — Tue, 14 Feb 2017 16:20:14 GMT

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).

Re: insert date macro variable into teradata

Shmuel — Tue, 14 Feb 2017 16:47:31 GMT

@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)));

Re: insert date macro variable into teradata

Tom — Tue, 14 Feb 2017 16:46:35 GMT

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.