Date:         Thu, 3 Mar 2005 12:11:51 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: OPTIMIZED WORK SPACE CODE FOR THE FOLLOWING CODE
Comments: To: sasguy <addanki007@GMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

SASguy:
Your posting does not include details (SAS Version, sizes of datasets,
nature of datasets etc.) that would help us assess the problem with work
space. Typically, a work space problem in SAS SQL involves a join query
that the SAS query optimizer cannot improve. In those cases it resorts
to a Cartesian product of datasets.

The SQL execution plan will tend to require a huge work space if, for
example, many multiple instances of the composite key PATID,<DRUG>
appear in both of the datasets being joined. Hopefully each of the
datasets has a distinct primary key. If not, you are asking for trouble
on several fronts.

Assuming that you are using a normalized database, you could run a quick
test to see if the version of the SAS SQL query optimizer that you are
using is missing an opportunity to improve the query. Try recasting the
query as an explicit inner join:

select .... from ... A inner join ... B on A.ID=B.ID and
A.<DRUG>=B.<DRUG> where A.<DATE> < B.<DATE> ...

Provided that the equijoin on ID and DRUG matches yields a reasonably
small number of rows, the inequality on <DATE> will work efficiently.

The SAS SQL compiler should recognize the equivalence of the two forms
of query, but who knows in this case. Start with the simplest possible
(and better form, to boot) fix. If the query without the date inequality
fails, an efficient solution will have to include some query improvement
tricks. Let me know
and I'll recommend more drastic fixes.
Sig
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
sasguy
Sent: Wednesday, March 02, 2005 2:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: OPTIMIZED WORK SPACE CODE FOR THE FOLLOWING CODE


HI,
Please help me regarding this code Iam running on mainframe
i used maximum of 4000 primary cyl and 1000 secondary cylinders I need a
alternative code which works wothout any work space problems Thannks in
advance  PROC SQL;
      CREATE TABLE ALL_PRIORRXS AS
      SELECT A.*,B.DRUGNAME AS PRIORDRUG,B.RXDATE
      AS PRIORDATE,B.DDS_SUPPLY,
      B.PRODDESC AS PRIORPROD,B.FORMDESC
      AS PRIORFORM,B.STRNDESC AS PRIORSTRN
      FROM SUB_INDEXRXS A,
      RXFILE B
      WHERE (A.PATID EQ B.PATID) AND
            (A.INDEXDRUG EQ B.DRUGNAME) AND
            (B.RXDATE<A.INDEXDATE);
 QUIT;