proc sql text qualifier

[BU2B]

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?

[Reeza]

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]

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]

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]

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 · Accepted solution]

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]

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

[Ksharp]

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

[Karthikeyan]

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