Macro: Display Only 1 Level of Resolution. (Advanced Question) :)

[jimbarbour]

BACKGROUND:

I run some explicit pass through SQL using EG 8.2 Update 1 (8.2.1.1223) (32-bit) running SAS 9.4 M6.  My code connects to a database (Hive, if it matters) using a connection string.  See code snippet, below.

I use the same code to connect to either our test or our production database.  I set a macro variable, Connection, to a macro variable that contains the value of connection string, either &ProdConn or &TestConn.  By setting Connection in the top of the program, I can run all subsequent steps without having to make any code changes.  The actual fully resolved value would be something like:  {SAS003}6390DF19407B4A09B0E12790731FE4E3844FE7A4

GOAL:

I want to display in the log which connection string was used, either &ProdConn or &TestConn, so that I can easily distinguish production runs vs. test runs.  I don't want to display the fully resolved value since I can't tell from something like {SAS003}6390DF19407B4A09B0E12790731FE4E3844FE7A4 whether that's production or test.  I want to display the literal &ProdConn or &TestConn, not the fully resolved value.  

QUESTION:

How can I display the literal &ProdConn or &TestConn, not the fully resolved value?

REMARKS:

I can make this work if I change my %LET statements to wrap the macro variables, either &ProdConn or &TestConn, in a %NRSTR().  However, in actual production code, a parameter file is used, not a hard coded %LET statement.  The parameter file just contains either &ProdConn or &TestConn.  I'd really like to just be able to determine which literal value was used, either &ProdConn or &TestConn.  This actually works in one of my programs, but I can't figure out how it's working.   Our Prod environment has many levels of macros calling macros.  So far I've been unable to replicate in new programs what one of our Production programs is already doing.  Maybe I'll eventually figure this out, but I'm guessing that I'm not the first person who has encountered this situation and that maybe somebody already knows how to do this. 

At the bottom of this post, I've included a sample of the working code and the working results from that code.

**------------------------------------------------------------------------------**;
**	Set Connection string to either the production value or the test value.		**;
**------------------------------------------------------------------------------**;
%LET	Connection	=	&ProdConn;
*%LET	Connection	=	&TestConn;

**------------------------------------------------------------------------------**;
**	I want to display the literal &ProdConn or &TestCon, not resolved value.	**;
**	How can I display either &ProdConn or &TestCon but not the resolved value?	**;
**------------------------------------------------------------------------------**;
%PUT	&Connection;

**------------------------------------------------------------------------------**;
**	The SQL connects using the fully resolved value of &ProdConn or &TestConn.	**;
**	Example:  {SAS003}6390DF19407B4A09B0E12790731FE4E3844FE7A4					**;
**------------------------------------------------------------------------------**;
PROC	SQL	NOPRINT;
	CONNECT	TO	ODBC							AS		DB_Cnx	("&Connection");

	EXECUTE(
	SET	hive.resultset.use.unique.column.names	=		false)
		BY	DB_Cnx;
	
	DISCONNECT 									FROM	DB_Cnx;
QUIT;

Thank you,

Jim

Below is the line of code that produces the results I want.  See sample results below that.  However, pulling this code into a new program gives me the fully resolved value, not the single level of resolution that I'm looking for.

%PUT	&Nte2  %Format_Line(%STR(  )Cnx_OPSI=%SUPERQ(Cnx_OPSI),				&Width);

Sample production results where this is already working (see third environmental parameter displayed below, Cnx_OPSI):

       +------------------------------------------------+
NOTE:  | Pgm _Allo parameters                           |
       +------------------------------------------------+
       | Environmental Parameters                       |
       |   Environment=Development                      |
       |       ** Is your Environment correct? **       |
       |   DB=OPSIprd                                   |
       |   Cnx_OPSI=&CnxOPSIprd                         |
       |   Catalog=                                     |
       |   Obs=MAX                                      |
       |   SysObs=MAX                                   |
       |   Mode=REGULAR                                 |
       |   ACA_Data=ACADATA                             |
       |   Sleep=50                                     |
       |   Delay_Opt=DELAY                              |
       |   Delay_Time=50                                |
       |   Prompt=PROMPT                                |
       |   PRINTTO=NOPRINTTO                            |
       |   Delete_All=NOKILL                            |
       +------------------------------------------------+

[Quentin · Accepted solution]

Hi,

For this to work, your macro variable CONNECTION needs to have the value &ProdConn or &TestConn.  The best way to see the value of the macro variable is to use %PUT _USER_ ; because it will shows you the value of CONNECTION without attempting any further resolution.

Note that if you submit:

%LET ProdConn   = Foo ;
%LET Connection	=	&ProdConn;
%put _user_ ;

You will see the CONNECTION has the value Foo, because &ProdConn is resolved when the %LET statement executes.

One option might be to use %NRSTR in the %LET statement.  If you do that, the macro trigger & will be hidden, so Connection will resolve to the text &ProdConn but will not resolve that further, unless you use %unquote.

%LET ProdConn    = Foo ;
%LET Connection	=	%nrstr(&ProdConn);

%put _user_ ;
%put &Connection ;
%put %unquote(&Connection) ;

Another option would be to use CALL SYMPUTX to write the value &ProdConn to CONNECTION.  If you use single quotes around the value, it will not attempt resolution. 

%LET  ProdConn    = Foo ;
data _null_ ;
  call symputx('Connection','&ProdConn') ;
run ;
%put _user_ ;
%put &Connection ;         *Resolves &Connection->&ProdConn->Foo ;
%put %superq(Connection) ; *Resolves &Connection->&ProdConn ;

A hack would be to we to run the assignment statements in the order below.  If &ProdConn does not exist when the %LET statement attempts to resolve it, it will issue a warning and the write the value &ProdConn to CONNECTION.  There are risks to this hack.

%symdel ProdConn Connection ;
%LET Connection	=	&ProdConn ;
%LET ProdConn    = Foo ;
%put _user_ ;

[jimbarbour]

Hi, Quentin, 

Thank you for that.  I eventually figured out that it was the way the macro variable was created (which affects how it is stored) that made the difference.  If the macro variable is stored as a macro variable reference through %LET Connection = %NRSTR(&ProdConn) or CALL SYMPUT('Connection', '&ProdConn', 'G'), then the value in the macro table is literally &ProdConn, not the fully resolved connection string.  Note the single quotes around &ProdConn in the CALL SYMPUT. 

Having the value stored as a symbolic reference is what allows %SUPERQ to work.  I was getting hung up on thinking it was how the macro variable was resolved.  No, not really.  It's how it is stored that is essential, not the way that it is resolved.  If stored appropriately, then %SUPERQ is all that is needed.

The actual line of code that in my case creates the variable from the parameter file is:

CALL	SYMPUTX(Var_Name, Var_Value, 'G');

Since Var_Value is a data step variable, no quotes are used, and the value is stored literally as either &ProdConn or &TestConn.  Had it been a literal value with single quotes around it, perhaps I'd have picked up on it sooner.

By the way, I really enjoyed the last BASUG that I attended.  The gentleman who presented (I think it was "how SAS thinks") was very knowledgeable.

Jim

[Tom]

If the value is available in a dataset then use SAS to print the values instead of the macro processor.  To the SAS language the & character is just like any other text.

data _null_;
  set macro_var_list;
  where var_name='CONNECTION';
  put var_name= var_value=;
run;

Or use SYMGET() to pull the value back into a dataset.

data _null_;
  length connection $200;
  connection=symget('connection');
  put connection=;
run;

[Tom]

Use a different macro variable.

Let's call it ENVIRONMENT and use PROD or TEST as the possible values.

%let environment=PROD;

So the see it in the LOG just use:

%put &=environment;

And to set your existing CONNECTION macro variable based on the value of ENVIRONMENT use:

%let connection=&&&environment.conn;

[jimbarbour]

Hi, Tom,

Good work around.  I like people coming in with a different slant as you just did.  Your method would be completely satisfactory (so long as everyone in the shop uses appropriate naming conventions).

HJ

[Tom]

Probably your other examples have used one of these two methods to populate CONNECTION.

data _null_;
  call symputx('connection','&prodconn');
run;
%let connect=%nrstr(&)prodconn;

To see what is actually in the macro variable use %SUPERQ() to quote its contents.

%put CONNECTION is %superq(connection) with value "&connection".;

[jimbarbour]

Yes, it is how it is stored that is critical.  That's a good technique, %PUT _USER_, to display the values.  I think  you also pointed out that %SUPERQ() works too.

A third way, if one is using SAS Enterprise Guide, is to use the SAS Macro Variable Viewer under "Tools".  I kind of like this since it keeps my log clean if I'm doing production work.

Jim

[LeonidBatkhan]

Would it be possible instead of using CONNECT TO ODBC to have LIBNAME statement to establish the connection? Then you can use something like this to suppress your passwords from showing in the SAS log:

%macro setlib(lib, env);
	filename hide temp;
	proc printto log=hide;
	run;
	%local pw;
	%if %upcase(&env)=PROD %then %let pw={SAS003}XXXX;
	%if %upcase(&env)=DEVL %then %let pw={SAS003}YYYY;
	libname &lib {libarary definition} PW="&pw";
	filename hide clear;
	proc printto log=log;
	run;
%mend setlib;

%setlib(DB_CNX,Devl);

PROC SQL NOPRINT;
	EXECUTE(
	SET	hive.resultset.use.unique.column.names=false)
		BY	DB_Cnx;
QUIT;

libname DB_CNX clear;

Would this work for you?

[jimbarbour]

Hi, Leonid.

Hmm.  Interesting idea.  I'll have to play with that when I get a chance.  

The one thing I do want to see in my log is where the password came from, either from &ProdConn or &TestConn.  I just don't want the {SAS003}XXXXX value to show because, a) I can't tell if it's a Prod or a Test run, and b) I don't want to have the password show in the log.

Jim