BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WillingWorker
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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 --<-----
BrunoMueller
SAS Super FREQ

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
Super User Tom
Super User

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
Barite | Level 11

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 --<-----
WillingWorker
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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