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

[Multipla99]

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)

[PaigeMiller]

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;

[Quentin]

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.

[PaigeMiller]

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.

[Multipla99]

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.
 

[PaigeMiller]

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.

[Multipla99]

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.

[PaigeMiller]

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.

[Tom]

---

@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.

[Quentin]

---

@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.

[Tom]

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;

[Tom]

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;

[Multipla99]

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. 

[Tom]

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;

[PaigeMiller]

---

@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.