Quotations on SQL pass through facility syntax

Reply
Contributor
Posts: 24

Quotations on SQL pass through facility syntax

Hello,

CURRENT SCENARIO:

proc sql noprint;

    connect to DATABASE (user="&userid" password=&pwd. server=NEWPROD database=SCHEMANAME);

SELECT *

  FROM CONNECTION TO DATABASE

   (

   SELECT *

   FROM SCHEMANAME.TABLENAME

);

QUIT;

MACRO &pwd resolves to PLAIN TEXT.

ie. &pwd resolves to PASSWORD.

Please note that there are NO quotations for macro &pwd in the above sql pass through syntax.


NEW SCENARIO:

&pwd is now encoded.

We use:

proc pwencode in="PASSWORD" METHOD=sas002;

run;

%let t_pwd={sas002}D51BD128501982BE1E8EEEFD1DEAB610

data _null_;

call symput ('pwd',"&t_pwd.");

run;

PROBLEM:

Now when i use the following sql pass through facility(Same as before):

proc sql noprint;

    connect to DATABASE (user="&userid" password=&pwd. server=NEWPROD database=SCHEMANAME);


I get the following error:

ERROR: Invalid option name sas002.


I can avoid getting this error if I write:

proc sql noprint;

    connect to DATABASE (user="&userid" password="&pwd." server=NEWPROD database=SCHEMANAME);

I have to put that quotation mark on &pwd.


The issue is that the sql pass through facility- WITHOUT the quotation, is present in all my codes.

There would be close to 100 different codes and 2-4 extraction steps in each code.

This would mean numerous changes.


How can I avoid changing ALL my sql pass through facility syntax?


Thanks

Grand Advisor
Posts: 17,325

Re: Quotations on SQL pass through facility syntax

Change the macro variable &pwd to resolve with quotes.

&pwd = "password"

Ask a Question
Discussion stats
  • 1 reply
  • 489 views
  • 0 likes
  • 2 in conversation