Date:         Thu, 21 Dec 2006 09:43:23 -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 Thu, 21 Dec 2006 06:09:20 -0800, hiemstra@yahoo.com <hiemstra@YAHOO.COM>
wrote:

>>    proc sql;
>>    reset inobs=5;
>>    select * from sashelp.class where sex='F';
>>    reset inobs=max;
>
>I tried this and it runs.  It does not, however, restrict the rows.

Yes it does. You get 5 rows, whereas with INOBS=MAX (the default) you get 9.

>
>> >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.
>
>Substituting the HAVING clause in, my SQL runs but I get an empty
>dataset.

If there are 1000 or fewer "SF" rows rows, you get them all. If there are
more than 1000, you get none. That's what the code is requesting.

>
>Stephen