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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.