Date:         Fri, 7 Apr 2000 16:17:49 GMT
Reply-To:     amichiel@EARTHLING.NET
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         amichiel@EARTHLING.NET
Organization: Deja.com - Before you buy.
Subject:      Re: row number with Proc sql / DB2

In pete2108@my-deja.com wrote:
>   "Lars Jacobsen" <laja@tdk.dk> wrote:
> > Using the OUTOBS=x means that SAS will read every row in the DB
> > table and only output x observations to the dataset ...
> > This however does not solve your problem with reading data only
> > from a certain rownumber.
> > I'm not sure if it's usefull but POINT jumps to mind.

> > "pete" <remarq2NOreSPAM@p.8m.com.invalid> wrote in message
> > > I need to extract a sekvential sample from a DB2-table (fetching
> > > for instance records from record number 100 to 119, and nothing
> > > else) to provide contents for a web-table .
> > > DB2 does not possess the concept of row numbers... this is a
> > > well-known fact.
> > > At present, I do an outobs limit (upper limit of record number)
> > > in my proc sql, and then a data step, subsetting with the _N_
> > > value (lower limit of record number).
> > > This work well when close to record 1, but when nearing end
> > > observation, I need to fetch the whole table in to a dataset.
> > > i.e... As we are using another extraction tool (SAS) we should be
> > > able to cheat/enhance DB2... RIGHT ?

It has been several years (and probably a couple version releases),
since I've used DB2/MVS, DB2/2, and DB2/6000, with or without SAS.
One of the things that our DB2 DBA's always harped about was that
the results (rows) from a query could be totally different from moment
to moment, running the same query.  IE, the results, in-total would
be the same, but the specific contents of each row could change from
moment to moment.  Personally, I never saw this happen, except when
data was refreshed, or index's tweaked.  However, if this claim is
basically accurate (and changes with index changes or data refresh
support that) then I'd think there is no fool proof or reliable way
to pull a random sample unless all the data was pulled in a single
query.
Given that assumption.  When you have a DBMS which is fully ANSI SQL-92
compliant (X3.135-1992), you should have the capability to use the
scrollable cursor.  The basic syntax would be something like:
DECLARE CURSOR cursor-name
FETCH orientation FROM cursor-name INTO target
The SQL-92 standard supports the cursor orientations of NEXT, PRIOR,
FIRST, LAST, ABSOLUTE n, RELATIVE n.  n can only from from -1 to rowmax.

So, it seems to me, that if you created a the values of the random
samle that you wanted, and loaded those as SAS Macro variables, that
you could then feed those macro variables as n to cursor position
commands to be used with a FETCH.  Written as 1 query, you shouldn't
have a problem with sample population changing structure during the
sampling, but of course, the sample may or may not be repeatable.
I see no reason why either the ABSOLUTE or RELATIVE cursor position
commands couldn't be used.  If you wanted a random sample of from
the table, you could just create a random number string in the range
from 1 to 10 and use the RELATIVE cursor postion to pull records.



Sent via Deja.com http://www.deja.com/
Before you buy.