topic Re: How to generate a non-defined macro variable without error messages? in SAS Programming

How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 17:10:55 GMT

The question below is related to the following answer: Solved: Macro error Apparent symbolic reference - SAS Support Communities

I have an example where code similar to below is run and the WARNING: and ERROR: messages similar to the related question are generated. According to the answer to that question "the only way num_obs is not defined is if the the first query returns no results".

However, how should mydata look like, not to generate any value into num_obs and at the same time return no error messages from the proc sql statement.

My log is very sparse, options nonotes turned on, and therefore I have no information about what the data set looked like at the time of running. It is also very difficult for me to rerun the program to regenerate the error, but nevertheless I need to understand what actually happened.

proc sql noprint;
select count(*) into :num_obs
from (select distinct var
from mydata);
quit;
%let num_obs=&num_obs.;

WARNING: Apparent symbolic reference num_obs not resolved.
ERROR: The text expression &num_obs. contains a recursive reference to the macro variable num_obs. The macro variable will be assigned the null value.

BTW: I'm running SAS 9.4 (TS1M6)

Re: How to generate a non-defined macro variable without error messages?

PaigeMiller — Mon, 16 May 2022 17:13:47 GMT

If I am understanding you properly, this is what you want:

%let num_obs=;
proc sql noprint;
    select count(*) into :num_obs
    from (select distinct var
    from mydata);
quit;

Re: How to generate a non-defined macro variable without error messages?

Quentin — Mon, 16 May 2022 17:25:19 GMT

Note that this statement:

%let num_obs=&num_obs.;

is not allowed. As the ERROR message says, the macro language sees this as a recursive reference that it can't handle.

In your case, I don't think you need a %LET statement at all. I think select count(*) will always return a value and create the macro variable NUM_OBS, unless there is an error in your code.

Re: How to generate a non-defined macro variable without error messages?

PaigeMiller — Mon, 16 May 2022 17:44:54 GMT

There are rare cases where count(*) does not return a value. Such as when the variable is not in the data set, which indeed would be a programming error.

proc sql;
    select count(*) into :macrovar from (select distinct gorilla from sashelp.class);
quit;
%put &=macrovar;

It is always a problem when the user selects tiny portions of the LOG instead of showing us the ENTIRE log for this PROC. @Multipla99 show us the ENTIRE log for the PROC of interest next time.

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 18:11:19 GMT

Thank you, PageMiller, for trying to find the answer to my question.

However, when I run your code I get the following error message:

ERROR: The following columns were not found in the contributing tables: gorilla.

I have no corresponding error message in my log.

Showing the log is of no use to anyone as the program starts with the following:

options nonotes nomprint nomlogic nosymbolgen nosource nosource2;

Thus it's only WARNING and ERROR that gets through to the log.

Re: How to generate a non-defined macro variable without error messages?

PaigeMiller — Mon, 16 May 2022 18:18:01 GMT

Run my first code, not the gorilla code which was just an example where an error occurs.

Showing the log is of no use to anyone as the program starts with the following:

options nonotes nomprint nomlogic nosymbolgen nosource nosource2;

Then issue the opposite command.

options notes source;

and show us the ENTIRE log for this PROC SQL and macro variable assignment code.

Re: How to generate a non-defined macro variable without error messages?

Tom — Mon, 16 May 2022 18:28:49 GMT

@Quentin wrote:

Note that this statement:
%let num_obs=&num_obs.;
is not allowed. As the ERROR message says, the macro language sees this as a recursive reference that it can't handle.

In your case, I don't think you need a %LET statement at all. I think select count(*) will always return a value and create the macro variable NUM_OBS, unless there is an error in your code.

That is not a recursive reference.

It is a common method to remove leading/trailing spaces from a macro variable that might have been introduced by using the old CALL SYMPUT() function or by the INTO clause of PROC SQL.

But it is easy to remove the need to remove those spaces by not creating to begin with:

In PROC SQL you can prevent the leading/trailing spaces by added the TRIMMED keyword.

In DATA step you can prevent the leading/trailing spaces by using the new (15 year old?) CALL SYMPUTX() function instead.

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 18:21:25 GMT

When I run your first code I get the following error message.

ERROR: File WORK.MYDATA.DATA does not exist.

However, this error message is also absent in my log.

Unfortnately it's very difficult for me to rerun the code to regenarate a log with more information.

Re: How to generate a non-defined macro variable without error messages?

Tom — Mon, 16 May 2022 18:24:02 GMT

If you are concerned the define the macro variable.

For example if you are using PROC SQL to define the variable then set some default value before executing the statement that you expect to assign a new value. Then you know the macro variable will exist. And it will also prevent you from using some stale value of the macro variable that was not replaced for some reason.

proc sql;
%let name=NOT FOUND;
select name into :name trimmed
  from sashelp.class
  where age > 30
;
quit;
%put &=name;

Re: How to generate a non-defined macro variable without error messages?

PaigeMiller — Mon, 16 May 2022 18:28:51 GMT

You, @Multipla99, in your original code, referred to data set MYDATA. You need to replace this with an actual data set name.

Unfortnately it's very difficult for me to rerun the code to regenarate a log with more information.

I don't believe that for a minute. You just ran the code and showed error messages. Just place the options right before your PROC SQL.

Re: How to generate a non-defined macro variable without error messages?

Tom — Mon, 16 May 2022 18:27:35 GMT

You can check if a macro variable is defined using %SYMEXIST() function and then define it.

%if not %symexist(num_obs) %then %do;
  %global num_obs;
%end;

Re: How to generate a non-defined macro variable without error messages?

Quentin — Mon, 16 May 2022 18:29:48 GMT

@Tom wrote:
@Quentin wrote:

Note that this statement:
%let num_obs=&num_obs.;
is not allowed. As the ERROR message says, the macro language sees this as a recursive reference that it can't handle.

In your case, I don't think you need a %LET statement at all. I think select count(*) will always return a value and create the macro variable NUM_OBS, unless there is an error in your code.
That is not a recursive reference.

It is a common method to remove leading/trailing spaces from a macro variable that might have been introduced by using the old CALL SYMPUT() function or by the INTO clause of PROC SQL.

In PROC SQL you and prevent the leading/trailing spaces by added the TRIMMED keyword.

In DATA step you can prevent the leading/trailing spaces by using the new (15 year old?) CALL SYMPUTX() function instead.

Thanks for the correction @Tom .

You are of course correct, %let foo=&foo; is only seen as an attempt to create a recursive reference if the macro variable FOO does not exist prior to the execution of this statement. In that case SAS is correctly avoiding the creation of a recursive reference. Indeed I had forgotten that this approach was common before the introduction of TRIMMED.

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 18:34:43 GMT

The problem is not that I want to check if a macro varible exist but rather how I got the messages:

WARNING: Apparent symbolic reference num_obs not resolved.
ERROR: The text expression &num_obs. contains a recursive reference to the macro variable num_obs. The macro variable will be assigned the null value.

Without having any error messages from Proc SQL.

Re: How to generate a non-defined macro variable without error messages?

Tom — Mon, 16 May 2022 18:46:38 GMT

It is easy to do, but not with COUNT(). Just run a query that does not return any observations. That does not cause any SQL error, But the INTO never writes to the macro variable since there is nothing to write.

I posted an example earlier.

234  proc sql noprint;
235  %let name=none;
236  select name into :name trimmed from sashelp.class where age>30;
NOTE: No rows were selected.
237  %put &=name;
NAME=none
238  quit;

Re: How to generate a non-defined macro variable without error messages?

PaigeMiller — Mon, 16 May 2022 18:42:30 GMT

@Multipla99 wrote:

The problem is not that I want to check if a macro varible exist but rather how I got the messages:
WARNING: Apparent symbolic reference num_obs not resolved.
ERROR: The text expression &num_obs. contains a recursive reference to the macro variable num_obs. The macro variable will be assigned the null value.
Without having any error messages from Proc SQL.

OK, you should still show us the ENTIRE log from PROC SQL, even if there are no errors, which is what I requested.

But, if you run my original code, changing MYDATA to the actual data set name, you should get a meaningful result.

Re: How to generate a non-defined macro variable without error messages?

Quentin — Mon, 16 May 2022 18:46:25 GMT

You may have had earlier errors in the job which caused SAS to enter SYNTAXCHECK mode.

You should try to make a little example that reproduces your problem.

The code you have shown does not generate the error (if a dataset work.mydata with a variable named VAR) exists:

%symdel num_obs /nowarn;
data mydata ;
  var=1;
run ;
proc sql noprint;
select count(*) into :num_obs
from (select distinct var
from mydata);
quit;
%let num_obs=&num_obs.;
%put >>&num_obs<< ;

Even if work.mydata has 0 obs, you don't get an error:

%symdel num_obs /nowarn;
data mydata ;
  var=1;
  stop ;
run ;
proc sql noprint;
select count(*) into :num_obs
from (select distinct var
from mydata);
quit;
%let num_obs=&num_obs.;
%put >>&num_obs<< ;

The only way I can see to get that error from a macro variable created via count(*) is if MYDATA doesn't exist, or if VAR doesn't exist. But in both of those instances, the SQL step would show an error.

proc delete data=mydata ;
run ;

%symdel num_obs /nowarn;
proc sql noprint;
select count(*) into :num_obs
from (select distinct var
from mydata);
quit;
%let num_obs=&num_obs.;

%put >>&num_obs<< ;


data mydata ;
  othervar=1;
run ;

%symdel num_obs /nowarn;
proc sql noprint;
select count(*) into :num_obs
from (select distinct var
from mydata);
quit;
%let num_obs=&num_obs.;

%put >>&num_obs<< ;

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 18:54:59 GMT

Thank you, Quentin, for the initiated reasoning.

I cannot be sure that mydata or var existed when running. However, I got no error message from Proc SQL so I assume the did. But, then how could I get an empty num_obs? I'm not sure if num_obs existed as a global and/or local macro variable before the proc sql was executed. But I think that the code might have been run within a SAS macro somehow.

The code is very complex.

Re: How to generate a non-defined macro variable without error messages?

Tom — Mon, 16 May 2022 19:00:17 GMT

@Multipla99 wrote:
Thank you, Quentin, for the initiated reasoning.

I cannot be sure that mydata or var existed when running. However, I got no error message from Proc SQL so I assume the did. But, then how could I get an empty num_obs? I'm not sure if num_obs existed as a global and/or local macro variable before the proc sql was executed. But I think that the code might have been run within a SAS macro somehow.

The code is very complex.

Another common mistake is to create a LOCAL macro variable and they try to reference it after the macro has ended.

%macro mymacro(ds);
proc sql noprint;
select count(*) into :num_obs trimmed from &ds;
quit;
%mend mymacro;

%mymacro(sashelp.class);
%put &=num_obs;

You can prevent this by just creating the macro variable before calling them macro. Then the INTO clause will write its value into the existing macro variable instead of making a new one.

%let num_obs=before;
%mymacro(sashelp.class);
%put &=num_obs;

259  %symdel num_obs / nowarn;
260  %mymacro(sashelp.class);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds


WARNING: Apparent symbolic reference NUM_OBS not resolved.
261  %put &=num_obs;
num_obs
262
263  %let num_obs=before;
264  %mymacro(sashelp.class);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


265  %put &=num_obs;
NUM_OBS=19

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 19:02:52 GMT

There is currently no log from PROC SQL due to the options set in the beginning for the batch of programs.

Re: How to generate a non-defined macro variable without error messages?

Multipla99 — Mon, 16 May 2022 19:08:49 GMT

I think you are closing in on the problem, Tom!

There is still, however, one little catch. If the proc sql is run within a SAS macro, the macro variable reference that generates the error message is run within the same macro.