topic Re: Failure to resolve macro variable in SAS Programming

Failure to resolve macro variable

WillingWorker — Mon, 27 Apr 2015 12:50:48 GMT

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.

Re: Failure to resolve macro variable

RW9 — Mon, 27 Apr 2015 13:50:05 GMT

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.

Re: Failure to resolve macro variable

jakarman — Mon, 27 Apr 2015 15:19:24 GMT

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?

Re: Failure to resolve macro variable

BrunoMueller — Mon, 27 Apr 2015 15:35:22 GMT

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.

Re: Failure to resolve macro variable

Tom — Mon, 27 Apr 2015 17:42:38 GMT

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;

Re: Failure to resolve macro variable

jakarman — Mon, 27 Apr 2015 17:48:08 GMT

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.

Re: Failure to resolve macro variable

WillingWorker — Mon, 27 Apr 2015 18:34:42 GMT

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.