Date:         Wed, 20 Dec 2006 21:48:49 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: SQL SAS for DATA step OBS= statement

On Wed, 20 Dec 2006 13:27:37 -0800, hiemstra@yahoo.com <hiemstra@YAHOO.COM>
wrote:

>What is the SAS SQL equivalent for a DATA step (OBS=1000) request?
>
>This works:
>
>DATA WORK.&_filename;
>    SET &_dir..&_filename(WHERE=(bus_type='SF') OBS=1000);
>RUN;

You can use the INOBS option, as in

   proc sql;
   reset inobs=5;
   select * from sashelp.class where sex='F';
   reset inobs=max;

This displays 5 rows, because the WHERE filter is applied before the counting.

>
>But I would like to optimize with a pass through SQL request to Oracle.

I don't know if Oracle supports this device. It's really an Oracle question,
not a SAS question. Fortunately Carol, who is knowledgeable regarding both
sides of that fence, was able to give you a good answer.

>
>I keep thinking that this syntax should work (it does not):
>
>PROC SQL FEEDBACK;
>        CREATE &vt WORK.&_filename AS
>        SELECT *, COUNT(*) AS ob_num
>        FROM &_dir..&_filename(WHERE=(bus_type='SF'))
>        WHERE CALCULATED ob_num LE 1000;
>QUIT;

IT depends on what you mean by "work". If you change the SQL WHERE clause to
a HAVING clause, the code should run. But it's all-or-nothing, because
COUNT(*) in the SELECT places the same overall row count into each row. So
it's not relevant to what you are trying to do.

>
>but my nagging suspicion is that I must do a GROUP BY and merge the
>result back.

GROUP BY what?

It *is* automatically merging the row count into.

  Is there a way to do this in a single pass?
>
>I would similarly like to create an SQL equivalent for a LAST statement
>using MAX().

LAST statement? Do you mean variables like LAST.SomeByVar in the DATA step?
That's not too similar to the current subject.

>
>Advice?
>
>Stephen