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, firstname.lastname@example.org <hiemstra@YAHOO.COM>
>What is the SAS SQL equivalent for a DATA step (OBS=1000) request?
> SET &_dir..&_filename(WHERE=(bus_type='SF') OBS=1000);
You can use the INOBS option, as in
select * from sashelp.class where sex='F';
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;
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
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
LAST statement? Do you mean variables like LAST.SomeByVar in the DATA step?
That's not too similar to the current subject.