Warning: Apparent symbolic reference not resolved? -But output looks right

[mitchell_keener]

Hi, 

I am currently using SAS 9.4.

I am working on a project which imports various specific ranges of cells in an excel document then does some manipulation on that data. The SAS program is driven by an excel file which has which ranges I want to pull, how to merge the tables, and what I want certain variables/tables to be named. The intention is to allow non-SAS users to enter the ranges they want and be able to combine various cell ranges together to create a table for reports. 

I have pasted the troublesome portion of the code below. The purpose of the following code is simple. I want to take whatever SAS decided to name my imported range of cells in excel (sometimes A, VAR1, or text from the document containing the data) and change it to the name I decided upon in the driver excel program. Each range imported will only have one variable. The way I decided to go about doing this is to utilize the vcolumn view to put the name of the current variable in the table of interest in a macro variable which is then used in a data step to allow me to set the name I want equal to the current name of the variable in the table. This must be dynamic since SAS is not always choosing the same name for the variable. This is all inside a macro because there are many tables being created and I would like a user to be able to add tables without having to add code. Currently, the code actually does what I want. The name I have chosen in my driver (var_name) is set equal to the current variable (&col). My issue is that, even though the code is performing as intended, I am still receiving the warning message for each line in my driver that is executed. Is there a reason why I am still receiving the warning even though it is apparently doing what I want? Even though the code works, my company will not be pleased with warning messages in the production code. I believe this has to do with how SAS is processing the macro variables but any further help would be greatly appreciated. 

Here is the code of interest:

/*rename column to var_name value*/
%macro renamer(name, var_name);

proc sql noprint;
select name
  into :col 
from sashelp.vcolumn as a where a.memname="&name"; 
quit;
run; 


data &name;
set &name;
&var_name=&col;
keep &var_name;
run;
%mend renamer;

data _null_;
set driver;
call execute( cats( '%renamer(',catx(',',name, var_name),')' ));
run;

WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
WARNING: Apparent symbolic reference COL not resolved.
NOTE: There were 7 observations read from the data set WORK.DRIVER.

I have attached a trimmed version of my driver if it is helpful. Thank you so much.

[Tom · Accepted solution]

You have a number of logic issues with that code.  Can you explain what you are trying to do?

Let me explain why you are getting those error messages and why it might appear that it works anyway.

An important thing to understand about using CALL EXECUTE() to push macro calls onto the stack to run after the current data step is that the macro processor will evaluate the string BEFORE it is pushed onto the stack.  But any CODE that the macro generates will be run when it is pulled back off of the stack AFTER the current data step finishes.  

Most likely the only reason it appears to work is because when you executed the CALL EXECUTE() statements the macro variable COL was NOT defined.  Hence the warnings.  Since the PROC SQL code that the macro generated did not yet execute the macro variable COL was not defined when the macro was trying to generate the code for the DATA step.

Since the macro variable was not defined the code that was pushed onto the stack contained literally the string &COL instead of having the macro reference replaced by the value of the COL macro variable.  So then later when SAS went to run the stacked up code it first ran the PROC SQL code and created the COL macro variable.  Then it ran the data step and now the reference to COL was able to be resolved.

To prevent this you can use the %NRSTR() macro function to temporarily quote the macro call reference so that the text of the macro call is pushed onto the stack and the execution of the macro is delayed until when the code is pulled back off to be run.

call execute( cats( '%nrstr(%renamer)(',catx(',',name, var_name),')' ));

[mitchell_keener]

Thank you for the response. What I am trying to do is take whatever SAS decided to name a range of cells I imported from an excel document and change the name of the variable to what I want it to be called. Sometimes the ranges have names already, but sometimes they do not and hence they come into SAS with the name 'VAR1' or 'A'. I want to take whatever SAS decided to name the variable and change it to what I have under 'Var_name' in my driver file. 

The complication is that I need this code to work no matter what cell ranges are added to the driver which is why I have written this as a macro. I could not figure out how to define the 'col' macro variable elsewhere because I need it to change each time the renaming is taking place for each table. 

I had thought what you mentioned might be what is occurring. Is there a way to alter the syntax so the macro processor ignores the &col on the first pass through the processor so the warning does not appear?

Please let me know if this explanation helps. Thank you. 

[mitchell_keener]

The code you have provided has solved the warning and my output is correct. Thank you Tom!

[Tom]

Your SQL logic is only finding ONE column name from the dataset.  You aren't even sorting by VARNUM to insure that it is the FIRST variable in the dataset.

[mitchell_keener]

You are correct, but it is intentional. Each table being imported will only ever have one variable. Each range of cells being imported will be a range of rows under one column. Certain columns are then combined using their position in the table. 

[ChrisNZ]

You should use SQL's union operator for this task.

This operator takes its names from the first table and ignore names from other tables.

data STRUCTURE;
  length A $8;
  stop;
run;
 
data HAVE;
  B='11';
run;

proc sql;
  create table WANT as 
  select * from STRUCTURE
  union
  select * from HAVE;
quit;

A
11

[mitchell_keener]

Hi Chris, 

This is also an interesting solution to my issue that may help. It is also simple. Thank you!