sas - Resolving multiple ampersands with macro variables

Question

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

Answer 1

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.