BookmarkSubscribeRSS Feed
csetzkorn
Lapis Lazuli | Level 10

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!

 

 

 

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

csetzkorn
Lapis Lazuli | Level 10
Thanks but it does not: ERROR: Teradata execute: Syntax error: Data Type "datepart" does not match a Defined Type name.
Tom
Super User Tom
Super User

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;

 

csetzkorn
Lapis Lazuli | Level 10
Thanks Tom. I am getting: ERROR: Teradata execute: A character string failed conversion to a numeric value.
Tom
Super User Tom
Super User

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.

csetzkorn
Lapis Lazuli | Level 10

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

Shmuel
Garnet | Level 18

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

 

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4064 views
  • 0 likes
  • 5 in conversation