4

This code works in SAS EG run on local (hidden sensitive information):

*---- two values: DEV (ALIASDEV) and PROD (ALIASPROD);
%let my_environment = ALIASDEV;
%let ALIASPROD= (hidden_tns_prod);
%let ALIASDEV= (hidden_tns_dev);

libname mylib oracle user=username password='my_password' path="&&my_environment";

But this code doesn't (with rsubmit;)

rsubmit;
*---- two values: DEV (ALIASDEV) and PROD (ALIASPROD);
%let my_environment = ALIASDEV;
%let ALIASPROD= (hidden_tns_prod);
%let ALIASDEV= (hidden_tns_dev);

libname mylib oracle user=username password='my_password' path="&&my_environment";
endrsubmit;

here is the error message:

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified. ERROR: Error in the LIBNAME statement.

What I am trying to do is having a macro (my_environment) that I can switch to work seamlessly between my dev and prod databases.

Thanks

Joe
  • 62,789
  • 6
  • 49
  • 67
Stephane Maarek
  • 5,202
  • 9
  • 46
  • 87

2 Answers2

4

I don't know why it worked on the local, but the ampersands require a third to resolve properly. Any time you store the value of a macro variable in another macro variable, you must use three ampersands to retrieve it.

Basic use cases:

  • Two ampersands allows you to resolve macro variables that contain other macro variables as part of the name. IE, if you have &val_sept and &val_oct, you can use &&val_&mon to retrieve it assuming %let mon=sept.
  • Three ampersands allow you to retrieve a macro variable that is contained as a value of another macro variable. So if you have &sept and &oct, then you would use &&&mon. to retrieve &sept from a variable %let mon=sept.

That's because of how multiple ampersands resolve; SAS makes multiple passes through until all are resolved.

In each pass:

  1. Every pair of ampersands resolves down to 1 ampersand, and is held aside.
  2. If there is a single ampersand left over, it resolves along with text after it as a macro variable, and is replaced by the value stored in such.

So:

%let x=a;
%let a=b;
%let b=c;

%put &&x;

1: &&x -> (&&)(x) -> (&)(x) -> &x 2: &x -> a

%put &&&x;

1: &&&x -> (&&)(&x) -> (&)(a) -> &a 2: &a -> b

%put &&&&x;

1: &&&&x -> (&&)(&&) (x) -> (&)(&)(x) -> &&x 2: &&x -> (&&)(x) -> (&)(x) -> &x 2: &x -> a

%put &&&&&x;

1: &&&&&x -> (&&)(&&)(&x) -> (&)(&)(a) -> &&a 2: &&a -> (&&)(a) -> (&a) 3: &a -> b

%put &&&&&&x;

1: &&&&&&x -> (&&)(&&)(&&) (x) -> (&)(&)(&)(x) -> &&&x 2: &&&a -> (&&)(&x) -> (&a) 3: &a -> b

Four ampersands is the most interesting to me, since adding one actually takes you back a step, effectively.

See my answer on sas MACRO ampersand for more detail.

Community
  • 1
  • 1
Joe
  • 62,789
  • 6
  • 49
  • 67
  • 2
    Note: whomever thought up TNS files, I'd like to say a thing or two to. The number of times they've been helpful is far outweighed by the difficulty in making sure they are consistently the same on various boxes, especially on servers that you don't have a privileged account on. – Joe Jul 22 '14 at 18:56
  • the statement was working before I used the double &. This was working: rsubmit; %let ALIASPROD = (tns hidden); libname mylib oracle user=username password='my_password' path="&ALIASPROD"; endrsubmit; – Stephane Maarek Jul 22 '14 at 18:58
  • 1268 %put &&aws_environment; is actually returning ALIAS_PROD instead of its content. It definitely comes from the double &. Any idea of why? – Stephane Maarek Jul 22 '14 at 19:05
  • That's how it should be - you need three &. Will edit answer when back at pc. That is also true on local, though. – Joe Jul 22 '14 at 19:06
  • Thanks! It is working now :) I don't understand why I need three &. Will be very interested in reading your answer soon. – Stephane Maarek Jul 22 '14 at 19:11
2

You may need an extra & in your path specification, so that it resolves to "(hidden_tns_dev)" instead of "ALIASDEV", like this: path="&&&my_environment" .

3eyes
  • 71
  • 1
  • 3