BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BU2B
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

8 REPLIES 8
Reeza
Super User

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?

BU2B
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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;

Haikuo
Onyx | Level 15

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

BU2B
Calcite | Level 5

Thanks guys!  Haikuo's code works fine.  Have a great day!

Ksharp
Super User

you don't need || macro variable is only to find and replace .

Karthikeyan
Fluorite | Level 6

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!!

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!

What is Bayesian Analysis?

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.

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
  • 8 replies
  • 1013 views
  • 0 likes
  • 6 in conversation