Hey all,
I have SQL code I'd like to pass 3 variables into. The date format I need is '31-OCT-2013'. So in my PROC SQL statement I current ly have this:
"'"||&LDay||'-'||&MON||'-'||&Year||"'"
What am I missing?
I run into this before when trying to accommodate Oracle requirement, which only takes single quotes. Here is what I have done and so far it works:
%LET DATE = %BQUOTE(')&LDay.-&MON.-&Year.%BQUOTE(');
then pass &DATE into your downstream SQL. Make sure your &LDay.,&MON.,&Year. do not have heading/trailing blanks. Or you can catenate them first in data step before trying to deliver it into Macro variables.
Good Luck,
Haikuo
1. Are you sure you need quotation marks?
2. Is that resolving with spaces in it?
3. Why do you think you need that format?
1. Are you sure you need quotation marks?
I've tried with them but still get errors.
2. Is that resolving with spaces in it?
?
3. Why do you think you need that format?
This is the only date format I've ever used in my SQL statements.
Can you post your code, with test data. Not sure why you would need to parse the date in such a way, if you do need that then try catx('-',&LDAY.,&MON.,&YEAR.) as RESULT_DATE.
You haven't posted why that isn't working, so it's hard to say.
You can see the extra spaces when it resolves, check the log. I did have to add extra quotes to the &MON to allow that to resolve.
%let lday=31;
%let mon=OCT;
%let year=2013;
data _null_;
sample="'"||&LDay||'-'||"&MON"||'-'||&Year||"'";
put sample;
run;
I run into this before when trying to accommodate Oracle requirement, which only takes single quotes. Here is what I have done and so far it works:
%LET DATE = %BQUOTE(')&LDay.-&MON.-&Year.%BQUOTE(');
then pass &DATE into your downstream SQL. Make sure your &LDay.,&MON.,&Year. do not have heading/trailing blanks. Or you can catenate them first in data step before trying to deliver it into Macro variables.
Good Luck,
Haikuo
Thanks guys! Haikuo's code works fine. Have a great day!
you don't need || macro variable is only to find and replace .
Hi
try this select &LDay || '-' || &MON || '-' || &Year as Date and pass the values as '31' , 'OCT' & '2014' . I don't know why you need the Quotes, for some reasons I can't get those quotes but the Double Quotes is working for me '" ' || &LDay || '-' || &MON || '-' || &Year || '"' .
Thanks!!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.