ERROR 388-185: Expecting an arithmetic operator, when assigning a variable to a column

[Gayle]

Does anyone see anything wrong with this code below specifically the bottom 2 lines

data final_validation;
	set dbtblnames_complete;
	tablename=newtblname1;
	call execute('%nrstr(%deterifcolsexist(&var1,&var2,' || strip(newtblname1) || '));');
	call execute('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
	%put &gsqlrc &gsqlmsg;
	sqlerrcode=&gsqlrc;
	sqlerrmsg=&gsqlmsg;

And i get the error below in the log:

3095  data final_validation;
3096      set dbtblnames_complete;
3097      tablename=newtblname1;
3098      call execute('%nrstr(%deterifcolsexist(&var1,&var2,' || strip(newtblname1) || '));');
3099      call execute('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
3100      %put &gsqlrc &gsqlmsg;
3807 Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist
3101      sqlerrcode=&gsqlrc;
3102      sqlerrmsg=&gsqlmsg;
NOTE: Line generated by the macro variable "GSQLMSG".
1     Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist
             -----------------------------------------
             388
             200
             76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

I am simply wanting to assign the error code and message to the output table: final_validation. I am not understanding what my syntax error is

[Reeza]

CALL Execute commands are executed after the data step is complete. If you're expecting a return code based on your macros running, this will not do that. Try DOSUBL instead which may allow for that type of functionality or you'll have to store them via a macro as well.

[Gayle]

? the statement above the errored one works fine. Something in the next assignment it doesn't like:
3102 sqlerrmsg=&gsqlmsg;
NOTE: Line generated by the macro variable "GSQLMSG".
1 Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist
-----------------------------------------
388
200
76

[Reeza]

You also need to put those in quotes, otherwise it things you're referring to a variable not a value. You'll also want to make sure the length is long enough otherwise it will initialize at the first value it receives.

length sqlerrcode sqerrmsg $256.;
sqlerrcode="&gsqlrc";
sqlerrmsg="&gsqlmsg";

Does it work in a brand new session where that variable may not exist yet? Are you sure you're getting the value expected and not something else? From everything I understand with how CALL EXECUTE works, that should not work but replacing it with DOSUBL (literal replace of CALL EXECUTE) will do what you expect. Or does that approach generate the same error?

[ballardw]

What the log is telling you with this bit:

3102      sqlerrmsg=&gsqlmsg;
NOTE: Line generated by the macro variable "GSQLMSG".
1     Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist

is that the SAS data step compiler saw this statement:

sqlerrmsg =  x_abc_d_work_05.CB_5138084_CHD_LOAD;

There are a number of objects in data step code that use a "value.keyword" or "keyword.value" code. Look up First. and Last. with By group processing or  HASH and the Report Writing Interface code. Since you use something not in quotes SAS thinks you are telling to use some form of Object because of the period in the middle and it doesn't find any of the expected types of keywords. So throws that particular error.

The "expected arithmetic operator" comes from the variable SQLERRMSG being an numeric variable and there was nothing on the right side of the = that resembled an "operation" that would result in a numeric value.

Macro variable references are resolved BEFORE the data step executes in the compilation phase also.

[Tom · Accepted solution]

 Since macro code is run before the SAS code it generates runs you are essentially running this.

%put &gsqlrc &gsqlmsg;

data final_validation;
	set dbtblnames_complete;
	tablename=newtblname1;
	call execute('%nrstr(%deterifcolsexist(&var1,&var2,' || strip(newtblname1) || '));');
	call execute('%nrstr(%tstuniqvalid(' || strip(newtblname1) || '))');
	sqlerrcode=&gsqlrc;
	sqlerrmsg=&gsqlmsg;
/* What is going on here? */

And we can see in the SAS log that the %PUT generated this line:

3807 Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist

So assuming 3087 is the value of gsqlrc then the last two lines of your posted SAS code is:

	sqlerrcode=3087;
	sqlerrmsg= Object 'x_abc_d_work_05.CB_5138084_CHD_LOAD' does not exist;

Which explains the error message since that last line is not a valid assignment statement.

Are you expecting SQLERRCODE and SQLERRMSG to be changed by the macro you pushed with CALL EXECUTE? 

First it cannot do anything to change any macro variables since you wrapped the call in %NRSTR() so nothing, not even macro code, will run until after the current data step stops.

Second if you removed the %NRSTR() so that the macro would run right away only the macro code would run. So any SQL statements that were generated that might change those two macro variables will not run until after the data step ends.

Third even if the macro is setting those macro variables with macro code you would need to use SYMGET() function call to get the result at the time the assignment statement runs instead of when the data step is compiled.

[Gayle]

Thank you! when I placed the variable in the SYMGET(): sqlerrmsg=symget('gsqlmsg'); it worked as expected!