Help using Base SAS procedures

proc sql text qualifier

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

proc sql text qualifier

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?


Accepted Solutions
Solution
‎04-28-2014 11:10 AM
Respected Advisor
Posts: 3,124

Re: proc sql text qualifier

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


All Replies
Super User
Posts: 17,960

Re: proc sql text qualifier

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?

Contributor
Posts: 33

Re: proc sql text qualifier

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.

Super User
Super User
Posts: 7,430

Re: proc sql text qualifier

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.

Super User
Posts: 17,960

Re: proc sql text qualifier

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;

Solution
‎04-28-2014 11:10 AM
Respected Advisor
Posts: 3,124

Re: proc sql text qualifier

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

Contributor
Posts: 33

Re: proc sql text qualifier

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

Super User
Posts: 9,691

Re: proc sql text qualifier

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

Contributor
Posts: 37

Re: proc sql text qualifier

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 313 views
  • 0 likes
  • 6 in conversation