SAS Proc SQL how to perform procedure only on N rows of a big table

Question

I need to perform a procedure on a small set (e.g. 100 rows) of a very big table just to test the syntax and output. I have been running the following code for a while and it's still running. I wonder if it is doing something else. Or what is the right way to do?

Proc sql  inobs = 100;
select 
   Var1, 
   sum(Var2) as VarSum
from BigTable
Group by
   Var1;
Quit;

Answer 1

What you're doing is fine (limiting the maximum number of records taken from any table to 100), but there are a few alternatives. To avoid any execution at all, use the noexec option:

proc sql noexec;
    select * from sashelp.class;
quit;

To restrict the obs from a specific dataset, you can use the data set obs option, e.g.

proc sql;
    select * from sashelp.class(obs = 5);
quit;

To get a better idea of what SAS is doing behind the scenes in terms of index usage and query planning, use the _method and _tree options (and optionally combine with inobs as above):

proc sql _method _tree inobs = 5;
    create table test as select * from sashelp.class
    group by sex
    having age = max(age);
quit;

These produce quite verbose output which is beyond the scope of this answer to explain fully, but you can easily search for more details if you want.

For further details on debugging SQL in SAS, refer to

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001360938.htm