proc sql outobs= triggers SAS warning

Question

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?

You could add more if/then logic to the runquit macro based on the levels documented here: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000208995.htm. — JimL, Oct 23 '15 at 18:52
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, Oct 23 '15 at 18:54
@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, Oct 23 '15 at 19:15
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, Oct 23 '15 at 19:19
What is your purpose of using `OUTOBS` in production context? Maybe the solution can be arrived at another way. — Joe, Oct 23 '15 at 19:19
@Joe I am wanting to keep the top N results of a group by statement sorted in descending order (similar to the example given). Think along the lines of 'Show top 10 customers by total value purchased'. The production code is more complex, ie. involves some calculated fields, joins and the like, and SQL seemed superior to alternatives from a readability perspective. — Robert Penridge, Oct 23 '15 at 19:29
Unless you are possibly wanting to pass the SQL into an RDBMS, I'd suggest going another way in a datastep. I don't think there is any way to either suppress or easily code around that warning (and coding around it would do nothing to improve readability). — JimL, Oct 23 '15 at 19:32
...and if your goal IS to pass the SQL into an RDBMS, the "outobs" option won't pass through in most if not all cases. — JimL, Oct 23 '15 at 19:32
@Joe Yes I think that sounds like it may be the best alternative. If you want to post that as the answer I'll accept... — Robert Penridge, Oct 23 '15 at 20:56

score 2 · Accepted Answer · answered Oct 23 '15 at 21:00

2

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;

answered Oct 23 '15 at 21:00

Joe

62,789
6
49
67

You could also use a view if you wanted to avoid the I/O. – Robert Penridge Oct 23 '15 at 21:47
@Quentin No unfortunately it doesn't. Example: `proc sql noprint; create table tmp(obs=1) as select * from sashelp.class ; quit;` – Robert Penridge Nov 16 '15 at 17:12

score 0 · Answer 2 · answered Oct 24 '15 at 19:51

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;

score 0 · Answer 3 · answered Oct 26 '15 at 01:31

0

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;

answered Oct 26 '15 at 01:31

Vishant

266
5
16

Actually this still triggers the `%abort cancel;` so it doesn't work in the scenario I'm working with. – Robert Penridge Nov 16 '15 at 17:14

proc sql outobs= triggers SAS warning

3 Answers3