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!
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.
Slight modification:
put(datepart(&RunDay.),YYMMDD.)
as &RunDay will be (and should be) a numeric value.
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;
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.
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).
@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)));
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.