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

[Multipla99]

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

[Quentin]

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<< ;

[Multipla99]

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.

[Tom]

---

@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

[Multipla99]

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.

[Quentin]

If your log is not useful because you've turned off notes, mprint, etc, I would dig through the code.  Ideally post the code and you can have more eyes.

As Tom mentioned, the typical whey I have had this problem is to have a WHERE clause that selects 0 records.  If your real code is not using COUNT(), this could create the error message you see:

1    proc sql noprint;
2      select name into :name trimmed
3        from sashelp.class
4        where age>30
5      ;
NOTE: No rows were selected.
6    quit;

WARNING: Apparent symbolic reference NAME not resolved.
ERROR: The text expression &NAME contains a recursive reference to the macro variable NAME.  The
       macro variable will be assigned the null value.
7
8    %let name=&name ;

Similarly, if you input data has 0 obs, that can create the error:

1    %symdel name/nowarn ;
2
3    data NoObs ;
4      set sashelp.class ;
5      stop ;
6    run ;

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NOOBS has 0 observations and 5 variables.

7
8    proc sql noprint;
9      select name into :name trimmed
10       from NoObs
11     ;
NOTE: No rows were selected.
12   quit;

WARNING: Apparent symbolic reference NAME not resolved.
ERROR: The text expression &NAME contains a recursive reference to the macro variable NAME.  The
       macro variable will be assigned the null value.
13
14   %let name=&name ;

But if your real code has COUNT() then the above can't explain it.

[Multipla99]

Thanks a lot for all the good ideas that you have provided! I will try to get some more information from the code and the log and, if I succed, share it with you.

[Patrick]

@Multipla99 

If I run your code as you initially posted it I can replicate the error message.

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

BUT

The very first ERROR message in the SAS log is:

Is the error you've shared with us the very first error or warning in your SAS log or could it be like in my case that what you've shared with us is just a consequence of earlier issues? Like here that table work.mydata doesn't exist.

[Multipla99]

Now I managed to get some more information into the log. It seems that there is some NOEXEC option set that will prevent the proc SQL from running, which explains why there are no error messages. Does someone how to turn on and off the option NOEXEC? I doesn't seem to be done in the concerned proc sql statement.

4276 +proc sql noprint;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
4277 + select count(*) into :antal_delreg
4278 + from (select distinct LKF
4279 + from Befolkningdata);
NOTE: Statement not executed due to NOEXEC option.
4280 +quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[Kurt_Bremser]

So you have an ERROR earlier in your code that sends SAS into syntax check mode.

Debug your code from the top down, until every step runs without ERRORs or WARNINGs and does what you intend it to do.

Otherwise you always have a case of GIGO.

[Multipla99 · Accepted solution]

To summarize: 

1. A previous error set SAS in syntax check mode which meant that procs were not executed.
2. Thus, the macro variable :num_obs, from my first example, was not generated. Since the syntax still was OK no error messages were presented.
3. Since the syntax check mode didn't prevent %let statement from being executed an error message was generated due to the missing macro variable.  

Thank you everyone for your input!