Failure to resolve macro variable

[WillingWorker]

Looking a suggestion on this as I have tried about everything. I am trying to write a generic DB connection where the user passes their ID by macro variable the code posted verifies the visibility of the macro but when used in the connect string, it is not recognized.

Code snippet

  %let USER_LDAP = %unquote(%bquote(')500000@LDAP%bquote('));

  %let connect_str = CONNECT TO teradata (user= &USER_LDAP password="%bquote(&)server_dbpass" tdpid = edtdp.XX.com  mode=teradata);

  %put &connect_str;             /* print out the string to ensure macro USER_LDAP is valid   */

  PROC SQL;

    CONNECT TO teradata (user= &USER_LDAP password="&server_dbpass" tdpid = edtdp.XX.com  mode=teradata);

        create table output as

        select * from connection to teradata

        ( &server_sql_statement );

  ENDRSUBMIT;

Corresponding Log output

CONNECT TO teradata (user= '500000@LDAP' password="&server_dbpass" tdpid = edtdp.XX.com  mode=teradata)

2 CONNECT TO teradata (user= &USER_LDAP password=XXXXXXXXXX tdpid = edtdp.XX.com mode=teradata);

WARNING: Apparent symbolic reference USER_LDAP not resolved.

[RW9]

Probably the line:

%let connect_str = CONNECT TO teradata (user= &USER_LDAP password="%bquote(&)server_dbpass" tdpid = edtdp.XX.com  mode=teradata);

You need user_ldap to resolve to someting within quotes, which currently it isn't.

Can you not do:

connect to tearadata (&connect_str.);

With regards to what your doing however, is this a good idea?  Its not really a big saving in terms of typing, and I can't see what vale its adding. 

[jakarman]

The log messages 
   CONNECT TO teradata (user= '500000@LDAP' password="&server_dbpass" tdpid = edtdp.XX.com  mode=teradata)

   2 CONNECT TO teradata (user= &USER_LDAP password=XXXXXXXXXX tdpid = edtdp.XX.com mode=teradata);

   WARNING: Apparent symbolic reference USER_LDAP not resolved.

is telling you there are issues with the USER_LDAP macro lifetime.  The code you postes must be incomplete.

What you are trying to do, looks very complicated, unnecessary complicated.
Ever thought on more simple approaches? What is the original questions issues and options?  

[BrunoMueller]

Hi

It looks like you are using SAS/CONNECT with RSUBMIT. Please have a look at the doc SAS/CONNECT(R) 9.4 User's Guide, Second Edition, Maybe you have to transfer a local macro variable to the remote system, so that the value can be resolved.

[Tom]

Sounds like you want to get the the "&server_dbpass" into the macro variable without macro quoting the & so that it will resolve?

Did you try %NSTR() instead of %BQUOTE().

Did you try using CALL SYMPUTX to put the string into the macro variable without macro quoting?

data _null_;

  call symputx

  ('connect_str'

  ,'CONNECT TO teradata (user= &USER_LDAP password="&server_dbpass" tdpid = edtdp.XX.com  mode=teradata)'

  );

run;

[jakarman · Accepted solution]

If we into guessing mode what willingworker is up to my guess is Bruno got closer to what is behind it.

Probably propagating local macro values to a remote session od doing that in a multi nested one. That is needing the %syslput as defined in the connect manuals.

Usage of the %let will cause that is executed on the wrong moment. More easily avoided by using a macro-definition on the remote session.

[WillingWorker]

Thanks for the suggestions, they got me thinking in the right direction and was resolved with some code rearrangement and a properly placed %syslput.

I appreciate the assistance.

WW