Date:         Mon, 31 Mar 2008 14:22:54 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SAS/Oracle Processing Question
In-Reply-To:  <403593359CA56C4CAE1F8F4F00DCFE7D0DF22A4D@MAILBE2.westat.com>
Content-Type: text/plain; charset="us-ascii"

The first link failed to link to a paper.  I found SAS(r) In-Database
Processing: A Roadmap for Deeper Technical Integration with
Database Management Systems at
http://support.sas.com/resources/papers/tnote/tnote_database.html

Much of the Roadmap concerns enabling of SAS features such as formats,
SAS functions, and statistical procedures with a DB environment: really
promising development but beyond the question of how to make key values
in a SAS environment available in a DB environment. To extract a dataset
from a DB that has an index defined on a key variable, try the
dbkey=<key> and  dbnullskey=NO options. See

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0705D&L=sas-l&P=R9478&D=1&H
=0&O=D&T=1

and the related thread.

Assuming that you have sufficient rights to create and populate tables
in a DB (though convincing DBA's to grant those rights may prove to be
the toughest hurdle), it works well to create a temporary table of
distinct key values on the DB side and an inner join to restrict
selections from a DB table to DB tuples with those key values. In
outline,

1. Define a SAS/ACCESS (Oracle, OLEdb, DB2, or other) connection string
to a DB in a LIBNAME statement (say, one named "lib");
2. create table lib.IDs as select distinct ID from <dataset>;
3. create table <dataset> as select t1.*
   from lib.<relation> as t1 inner join lib.IDS as t2 ON t1.ID=t2.ID
   where DBdate between mdy(<m>,<d>,<y>) AND .....
     AND .... ;

This method will support compound keys and will limit the yield of the
program on the DB side to relevant tuples.
S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Michael Raithel
Sent: Monday, March 31, 2008 12:41 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: RE: SAS/Oracle Processing Question


Dear SAS-L-ers,

Richard Wright posted, in part, the following helpful information:

<<Richard's entire original posting can be found beneath the Sig line>>

> 1. A couple of months ago, I read a white paper on the SAS website
> that they were starting offload some of the processing onto the
> server. I don't have it handy, but you might see if you can find it.
>
Richard, yea; that was a very interesting white paper, indeed!  There
was a paper on in-database processing presented at SAS Global Forum
2008:

SAS(r) In-Database: Deeper Integration between the SAS System and
Database Management Systems, by David Shamlin:
http://www2.sas.com/proceedings/forum2008/315-2008.pdf

...and a second paper by the Teradata folks:

Driving Competitive Advantage with In-Database Analytics, by Mike Rote
http://www2.sas.com/proceedings/forum2008/062-2008.pdf

Perhaps this will eliminate the need for you to dig for that paper
reference that wasn't on the tip of your brain when you posted!

Richard, best of luck in all of your SAS endeavors!

I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not
reflect those of my organization or my associates. All SAS code and/or
methodologies specified in this posting are for illustrative purposes
only and no warranty is stated or implied as to their accuracy or
applicability. People deciding to use information in this posting do so
at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments,
Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
As long as you derive inner help and comfort from anything, keep it. -
Mahatma Gandhi
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<<Richard's entire original posting>>

> Couple of quick ideas:
>
> 1. A couple of months ago, I read a white paper on the SAS website
> that they were starting offload some of the processing onto the
> server. I don't have it handy, but you might see if you can find it.
>
> 2. There was a presentation at our SCSUG last fall about replacing SQL

> queries with hash tables - "How Do I Love Hash Tables? Let Me Count
> The Ways!" Judy Loren
>
> 3. Could you break up you query a little bit:
>
> create table local.claimmerge as
>   select vars
>   from
>   (select DISTINCT s1.id
>    from local.eligibility(KEEP=id) s1, local.type1claims(KEEP=id) s2
>    where s1.ID=s2.ID) a,
>    local.eligibility, local.type1claims
>   where a.id = eligibility.id and a.id = type1claims.id
>   order by ID;
>
> 4. Modify it further:
>
> proc sql noprint;
>   create table local.claimmerge as
>   select vars
>   from local.eligibility,
> dw.type1claims
>   where (eligibility.ID=type1claims.ID) and (type1claims.date between
>   1/1/2004 and 12/31/2006)
>   order by ID;
> quit;
>