CODEWITHSUNDEEP
sasproc-sql
Robert Penridge
Robert Penridge

Reputation: 8513

proc sql outobs= triggers SAS warning

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?

Upvotes: 3

Views: 5036

Answers (3)

Vishant
Vishant

Reputation: 266

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;

Upvotes: 0

Tom
Tom

Reputation: 51566

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;

Upvotes: 0

Joe
Joe

Reputation: 63424

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;

Upvotes: 2

Related Questions