Date: Wed, 30 Oct 2002 00:00:24 +0100
Reply-To: Peter Crawford <peter.crawford@DB.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Peter Crawford <peter.crawford@DB.COM>
Subject: Re: How to return the rows number of a data set?
Content-type: text/plain; charset=iso-8859-1
the only context where this might bother
me, always seems to work
proc sql noprint ;
select something
into :list separated by ' '
from somewhere
where <IwantIt> ;
%let n_wanted = &sqlobs;
quit;
has "so-far" been reliable ! (thank goodness)
Is this because that "separated by ...." forces the output.of
more than one row ?
I understand that without "separated by" or a range of mVar names,
only one row is output into the mVar.
That would appear to be consistent with having
&sqlObs = 1
The issue may be just what &sqlObs is counting...
It appears to be only rows output......
Kind Regards
Peter Crawford
Datum: 29/10/2002 23:48
An: SAS-L@LISTSERV.UGA.EDU
Antwort an: "Karl K." <karlstudboy@HOTMAIL.COM>
Betreff: Re: How to return the rows number of a data set?
Nachrichtentext:
On Tue, 29 Oct 2002 16:37:16 -0500, Ian Whitlock <WHITLOI1@WESTAT.COM>
wrote:
>Jack,
>
>It doesn't look accurate in my test.
>
>1645 data w ;
>1646 do x = 1 to 5 ; output ; end ;
>1647 run ;
>
>NOTE: The data set WORK.W has 5 observations and 1 variables.
>NOTE: DATA statement used:
> real time 0.11 seconds
>
>
>1648
>1649 proc sql noprint ;
>1650 select * from w ;
>1651 %put &sqlobs ;
>1
<snipped>
Has this odd "gotcha" been discussed in SAS-L before? If so, my apologies
and please read no further. If not, then my question is, has anybody else
ever found this behavior of sqlobs awfully counter-intuitive? In the on-
line documentation, "Using Macro Variables Set by Proc SQL", the example
in fact demonstrates the precise behavior Ian points out: if noprint is
set, and you just do a select, sqlobs=1, regardless of how many rows would
have been returned if noprint were off. Remove the noprint, and you get
(the correct) 5. Leave the noprint in and add a "create table as. . ."
and you get (also intuitively correct) 5. But just do a select with
noprint, you get 1. At the very least, shouldn't it be 0? There are a
couple other gotchas with sqlobs that escape me now.
In most of the other SQL implementations of which I'm aware, the
corresponding system variable contains the # of rows returned, period. I
think SI might want to consider changing the function of this variable, or
at least documenting it with more than just a "for example." Any thoughts?
Karl
--
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
