Failure to resolve macro variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Failure to resolve macro variable

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.


Accepted Solutions
Solution
‎04-27-2015 01:48 PM
Valued Guide
Posts: 3,208

Re: Failure to resolve macro variable

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.

---->-- ja karman --<-----

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: Failure to resolve macro variable

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. 

Valued Guide
Posts: 3,208

Re: Failure to resolve macro variable

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?  

---->-- ja karman --<-----
SAS Super FREQ
Posts: 706

Re: Failure to resolve macro variable

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.

Super User
Super User
Posts: 6,845

Re: Failure to resolve macro variable

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;

Solution
‎04-27-2015 01:48 PM
Valued Guide
Posts: 3,208

Re: Failure to resolve macro variable

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.

---->-- ja karman --<-----
Occasional Contributor
Posts: 5

Re: Failure to resolve macro variable

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 299 views
  • 3 likes
  • 5 in conversation