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 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
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.
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.
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.
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
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.
To summarize:
Thank you everyone for your input!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: