This question comes up from time to time. From your question I would guess that you are using PROC ACCESS in SAS V6. I have not used PROC ACCESS for a while, so I can't remember to what extent that procedure allows passing of a WHERE clause to your database (DUR does not tell us much about your data source). In SAS SQL you would want to embed a WHERE clause in a string that SAS can pass through to your server database. You would write something like

<connection to RDBMS DUR> create table clientDS as select * from (connection to <RDBMS> select * from <DUR> where ICD9 between x and y) ;

Whether you can specify a between clause or have to write out the upper and lower bounds depends on the RDBMS. The subquery in parentheses gets passed as a query to the RDBMS first. It must conform the syntax of the RDBMS, not to SAS SQL.

As a rule the savings in runtime come from 1) faster server performance due to processor speed and indexing; and 2) less time need to transfer smaller yields of queries across a relatively slow network. Under SAS V8, you may not have to worry about where you place the WHERE clause; SAS Connect will in some cases optimize the query. Some of the recent work at IBM looks at relative "costs" of distributing processing across servers versus data transfers. Sig

____________________Reply Separator____________________ Subject: [Fwd: [Fwd: Big data users]] Author: Robert Alsbrooks <ralsbroo@CCEB.UPENN.EDU> Date: 5/2/2000 1:44 PM

Hi All,

Can some give some advice on the problem outlined below.

Robert

Jesse Chittams wrote:

> Hi Robert, > > Please forward this to the SAS discussion group. > > Thanks, > -Jesse C. > ---------------------------------------------------------------- > > Subject: Big data users > Resent-From: bac@cceb.upenn.edu > Date: Thu, 27 Apr 2000 10:50:17 -0400 > From: "Jill Santanna" <jsantann@cceb.upenn.edu> > To: Anita Weber <aweber@cceb.upenn.edu> > , Colleen Brensinger <cbrensin@cceb.upenn.edu> > , Jill Santanna <jsantann@cceb.upenn.edu> > , Stephen Durborow <sdurboro@cceb.upenn.edu> > , Warren Bilker <wbilker@cceb.upenn.edu> > , bac <bac@cceb.upenn.edu> > > Hi Big Data Users!! > > I've been pulling records from the DUR database and after six months > I've finally come to the conclusion that there has got to be a more > time efficient way than how I'm doing this. > > What I'm trying to do is pull all of the cancer records. I want any > record that has an icd-9 code between 140 and 238.9. Is there a way > to create a view to do that instead of having to do the work in SAS? > Can you use the between statement in the Proc Access step to deal with > the character icd-9 codes as well? I've used it for dates but never > character strings. > > Any direction you could give me to improve this process would be > helpful. It took 7 days of real time (5 hrs CPU) to do this the last > time!! > > Thanks! > > Jill