topic Re: proc sql text qualifier in SAS Procedures

proc sql text qualifier

BU2B — Mon, 28 Apr 2014 14:51:51 GMT

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?

Re: proc sql text qualifier

Reeza — Mon, 28 Apr 2014 14:54:59 GMT

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?

Re: proc sql text qualifier

BU2B — Mon, 28 Apr 2014 14:58:01 GMT

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.

Re: proc sql text qualifier

RW9 — Mon, 28 Apr 2014 15:01:32 GMT

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.

Re: proc sql text qualifier

Reeza — Mon, 28 Apr 2014 15:05:37 GMT

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;

Re: proc sql text qualifier

Ksharp — Mon, 28 Apr 2014 15:10:18 GMT

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

Re: proc sql text qualifier

Haikuo — Mon, 28 Apr 2014 15:10:40 GMT

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

Re: proc sql text qualifier

BU2B — Mon, 28 Apr 2014 15:15:38 GMT

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

Re: proc sql text qualifier

Karthikeyan — Mon, 28 Apr 2014 15:20:57 GMT

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