Resolving a (SQL) macro variable inside macro in a remote session

[LE4]

Hello,

I have written a macro which submits code to a remote session and pulls data out of a DB2 table. 

Usually the macro variables "sqlxrc" and "sqlxmsg" are created and contain potential error messages.

This does not work if I put the (more or less) same code inside the aforementioned macro.

The Code looks like this:

[...]

%macro lorem_ipsum;
	RSUBMIT;

	PROC SQL;
		CONNECT TO db2 (connection=global);

		CREATE TABLE dolor_sit
		AS SELECT *
		FROM connection to db2(
			SELECT *
			FROM amet
		);

		%PUT &sqlxrc &sqlxmsg;		
                DISCONNECT FROM db2;
	QUIT; RUN;
	ENDRSUBMIT;
%mend lorem_ipsum;

[...]

When I run the macro, I get the warnings:

WARNING: Apparent symbolic reference SQLXRC not resolved.
WARNING: Apparent symbolic reference SQLXMSG not resolved.

So I assume the macro wants to resolve the macrovaribles before sending the code over to the remote session (?).

Can I somehow mask the varibles so that they get resolved when the actual SQL statement is processed?

Best regards

LE4

[PaigeMiller]

The remote session does not know what these macro variables are, they have not been defined to the remote session.

You either have to give these macro variables a value inside the RSUBMIT block, or use %SYSLPUT to define the macro and give it a value on the remove server. https://documentation.sas.com/doc/en/pgmmvacdc/9.4/mcrolref/p13mg8ttnegfzxn15lz4aky4d3p5.htm

[LE4]

Hello Paige,

unfortunatly that is not the case.

The code works perfectly fine as long as it is not inside the macro.

These 2 variable are created by the SQL procedure (see also: Macro Variables and System Options : Automatic Macro Variables (sas.com) ).

But if I want to run the code through a macro, the macro wants to resolve them before the SQL procedure even started (that's what I assume, not sure though)

Best regards

LE4

[Tom]

Why not define the macro on the remote SAS session and call it there?

RSUBMIT;
%macro lorem_ipsum;

	PROC SQL;
		CONNECT TO db2 (connection=global);

		CREATE TABLE dolor_sit
		AS SELECT *
		FROM connection to db2(
			SELECT *
			FROM amet
		);

		%PUT &sqlxrc &sqlxmsg;		
                DISCONNECT FROM db2;
	QUIT;
%mend lorem_ipsum;
ENDRSUBMIT;

RSUBMIT;
  %lorum_ipsum;
ENDRSUBMIT;

[LE4]

Hello Tom,

thanks for the suggestion. This could work maybe.

But this would also mean a lot more work since there is more code inside the macro which isn't relevant to this specific problem.

I would prefer to avoid that but if there is no solution to my problem then I guess, I would consider this.

Best regards

LE4

[LE4 · Accepted solution]

I think I found the solution:

SAS Help Center: %STR and %NRSTR Functions

%NRSTR seems to do the trick for me. So I basically put the whole line inside the %NRSTR function which results in the macro compiling the code without resolving the 2 variables ... I think ... so far I do not see a problem ...

Thanks for your suggestions anyway! 🙂

[Tom]

You can see the use of %NRSTR() for dealing with remote submission in the code of the old %SYSLPUT() autocall macro from SAS version 6.12. 

SAS has since introduced a %SYSLPUT statement since then so if you want to use that technique you need to define the macro with a different name.  Like this copy https://github.com/sasutils/macros/blob/master/syslput612.sas 

[LE4]

Hello Tom,
do you mean my macro lorem_ipsum? I'm afraid, I dont quite understand what you mean here... Sorry, I'm kinda new to SAS

[Tom]

Just another example of using %NRSTR() to alter the timing of when the macro variables are resolved.

You might end up needing it to push macro variable values defined in the local session that is executing your macro to the set the value of a macro variable you want to use in the remote session that is running the SQL code.

Another thing you can use to allow the setting of remote macro variable to control the execution of your local macro is to push the value of the remote macro variable back to your local session using the %sysrput macro statement.

So your flow might be something like:

%macro local_macro ;

rsubmit;
  proc sql ....
 %sysrput local_sysxrc = &sqlxrc;
endrsubmit;

%put &=local_sysxrc ;

%mend local_macro ;

%local_macro;
  

Try that and see if the proper value of the remote SYSXRC variable is actually transfered back to the local session.  You could then see whether if later in a second RSUBMIT block (or even in the same rsubmit block) you can use this local macro variable to control the code you are trying to run in the rsubmit block.

Or perhaps you need actually use code like:

%sysrput local_sysxrc = %nrstr(&sysxrc);

To get the value of the remote variable returned to your macro to use.

[LE4]

Ahhhh ok, thank you so much! 🙂