Date:         Mon, 21 May 2012 10:56:15 -0400
Reply-To:     Dave <david.brewer@UC.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Dave <david.brewer@UC.EDU>
Subject:      Proc SQL efficiency help needed

Hi all,

I am running 64bit SAS ver 9.3 on a terminal services box (Windows Server
2008 R2).

The following code takes over 31 hours to run:

3492  proc sql;
3493    create table outpatient as
3494    select a.*, i.ssn, i.case
3495    from op.seie&opyr as a, index4 as i
3496    where a.scrssn=i.scrssn and i.indexhosp=1 and i.admit-365 le
a.vizday lt i.admit
3497    order by a.scrssn;
NOTE: Table WORK.OUTPATIENT created, with 16188194 rows and 37 columns.

3498  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           30:32:07.05
      cpu time            1:57:33.64

Neither data set is indexed; op.seie&opyr contains 721,439,576 obs with 35
variables (135gb) and index4 contains 699,645 observations.

Does anyone have any tips on how to speed this query up or make the code
more efficient?

Thanks for your help.
Dave