proc sql outobs= triggers SAS warning

3
votes

We currently use the %runquit macro function as detailed here (http://analytics.ncsu.edu/sesug/2010/CC07.Blanchette.pdf). The %runquit macro is shown below. It basically stops running any more SAS code when an error is encounterd, and can be used as a replacement for both the run and quit statements:

%macro runquit;
  ; run; quit;
  %if &syserr %then %abort cancel;
%mend;

Because using the outobs statement in proc sql triggers a system error (even when the nowarn option is specified) it means we are unable to use the %runquit macro when we need to use the outobs= option.

The below example will generate the following warning message:

proc sql noprint outobs=3 /*nowarn*/;
  create table tmp as
  select age, count(*) as freq
  from sashelp.class
  group by 1
  order by 2 desc
  ;
%runquit;

WARNING: Statement terminated early due to OUTOBS=3 option.

Thank you SAS for the completely unnecessary warning. The behaviour is obviously expected because I explicitly wrote code to ask for it. I don't see warnings given when we specify inobs= and outobs= on a set statement. Why does proc sql get the special treatment?

Is there any way to disable the warning issues by the outobs= option in proc sql? Alternatively, is there another way to limit the output rows from proc sql that will not generate an error?

3
sasproc-sql
You could add more if/then logic to the runquit macro based on the levels documented here: support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/…. - JimL
I'd guess that proc sql is different because inobs and outobs are non-standard SQL. Most SQL flavors use a "limit" statement, which is not supported in proc sql. - JimL
@JimL Nice idea but unfortunately that's a pretty big bucket of errors I would be letting it opt-out of. The code returned is syserr=4 which corresponds to "Execution completed successfully but with warning messages". This could be anything from automatic type conversions to uninitialized variables, ie. the kind of things I'm trying to catch in the first place. - Robert Penridge
ah. sorry. I assumed that you were using the outobs option only when testing, not when running in production. I'll give it some more thought. - JimL
What is your purpose of using OUTOBS in production context? Maybe the solution can be arrived at another way. - Joe

3 Answers

2
votes

Assuming you are okay with the full SQL statement executing, you can get around this with a data step view that contains the obs limitation.

proc sql noprint ;
  create table tmp as
  select age, count(*) as freq
  from sashelp.class
  group by 1
  order by 2 desc
  ;
%runquit;

data tmp_fin/view=tmp_fin;
  set tmp(obs=3);
%runquit;
0
votes

Or make the SQL statement a view and use the data step to make the data set.

proc sql noprint ;
  create view tmp_view as
  select age
       , count(*) as freq
  from sashelp.class
  group by 1
  order by 2 desc
  ;
quit;
data tmp;
  set tmp_view(obs=3) ;
run;
0
votes

This might be one of your options considering I/O is not a huge constraint, here the reset outobs= option with nowarn does the trick but at IOs cost.

proc sql;
  create table test as
   select * from sashelp.class;

  reset outobs=10 nowarn;

  create table test1 as 
   select * from sashelp.class;
quit;