Date:         Wed, 30 Jan 2002 08:40:34 -0600
Reply-To:     "McKown, John" <JMckown@HEALTHAXIS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "McKown, John" <JMckown@HEALTHAXIS.COM>
Subject:      Re: SAS 8.0 loops
Content-Type: text/plain; charset="iso-8859-1"

Well, I found a solution to my problem. Basically, I forgot to mention that
the SAS dataset that I was adding to had a number of INDEXes defined on it.
When I deleted the INDEXes, did a PROC APPEND with the (CNTLLEV=MEMBER),
then rebuilt the INDEXes, the job ran very quickly. Apparently, at least on
OS/390 SAS 8.0, updating/adding observations to a dataset with INDEXes is
VERY slow and I/O intensive.

Thought others might to like to know.

--
John McKown
Applications and Solutions Team
Healthaxis



> -----Original Message-----
> From: McKown, John [SMTP:JMckown@HEALTHAXIS.COM]
> Sent: Monday, January 28, 2002 1:53 PM
> To:   SAS-L@LISTSERV.UGA.EDU
> Subject:      [SAS-L] SAS 8.0 loops
>
> Has anybody else noticed SAS looping when trying to add observations to an
> existing, multivolume SAS database? I am loading RACF information,
> generated
> by the IRRADU00 utility, into a SAS database for reporting purposes. At
> present, I keep about 6 months worth of information on the disk resident
> database. I keep "everything" on a tape resident database. The job to
> create
> these databases looks like:
>
> //STEP002  EXEC  SAS8,
> //             TIME.SAS8=NOLIMIT
> //TAPEDB   DD  DSN=TSHPN.IRRADU00.SASDB,
> //             DISP=SHR
> //SASDB1   DD  DSN=TSHPG.JOBINIT.SASDB(0),
> //             DISP=OLD
> //SASDB2   DD  DSN=TSHPG.JOBINIT.SASDB(+1),
> //             DISP=(NEW,CATLG),
> //             UNIT=CARTO,
> //             LABEL=(1,SL,EXPDT=99000),
> //             VOL=(,RETAIN,,35)
> //SASPGM   DD  DSN=TSH009.LI.SAS,
> //             DISP=SHR
> //SYSIN    DD  *
> OPTIONS ERRORS=1 NOCENTER SORTDEV=3390;
> DATA SASDB2.JOBINIT;
>      SET SASDB1.JOBINIT
>          TAPEDB.JOBINIT;
> RUN;
> //*
>
> The step to create the disk resident database is:
>
> //STEP003  EXEC  SAS8,
> //             TIME.SAS8=NOLIMIT
> //WORK     DD  UNIT=(,4),
> //             SPACE=(CYL,(1000,200))
> //SASDB    DD  DSN=TSHPN.JOBINIT.SASDB,
> //             DISP=(NEW,CATLG),
> //             UNIT=(SYSDA,5),
> //             SPACE=(CYL,(600,200)),
> //             RECFM=FBS,LRECL=27998,
> //             BLKSIZE=27998
> //SASDB2   DD  DSN=TSHPG.JOBINIT.SASDB(+1),
> //             DISP=OLD
> //SASPGM   DD  DSN=TSH009.LI.SAS,
> //             DISP=SHR
> //SYSIN    DD  *
> OPTIONS ERRORS=1 NOCENTER SORTDEV=3390 BLKSIZE=27998;
> PROC SQL;
>      CREATE TABLE SASDB.JOBINIT AS
>      SELECT *
>             FROM SASDB2.JOBINIT
>             WHERE INIT_DATE_WRITTEN >= INTNX('MONTH',TODAY(),-7)
>      ;
> PROC DATASETS LIBRARY=SASDB;
>      MODIFY JOBINIT;
>      INDEX CREATE  INIT_EVT_USER_ID ;
>      INDEX CREATE  INIT_APPL ;
>      INDEX CREATE  INIT_EVT_GRP_ID ;
>      INDEX CREATE  INIT_UTK_USER_ID ;
>      INDEX CREATE  INIT_UTK_GRP_ID ;
>      INDEX CREATE  INIT_DATE_WRITTEN ;
> RUN;
> PROC SQL;
>      SELECT MIN(INIT_DATE_WRITTEN) FORMAT=YYMMDD10. ,
>             MAX(INIT_DATE_WRITTEN) FORMAT=YYMMDD10.
>      FROM SASDB.JOBINIT
>      ;
> /*
> //*
>
> What I would like to do instead of the second step, which subsets the
> entire
> tape, is to update the disk resident database using either PROC SQL or
> PROC
> APPEND. I have tried both. The following is my PROC SQL step to do this:
>
> //STEP001  EXEC  SAS8,
> //             TIME.SAS8=NOLIMIT
> //WORK     DD  UNIT=(,4),
> //             SPACE=(CYL,(1000,200))
> //TAPEDB   DD  DSN=TSHPN.IRRADU00.SASDB,
> //             DISP=SHR
> //SASDB    DD  DSN=TSHPN.JOBINIT.SASDB,
> //             DISP=OLD
> //SASPGM   DD  DSN=TSH009.LI.SAS,
> //             DISP=SHR
> //SYSIN    DD  *
> OPTIONS ERRORS=1 NOCENTER SORTDEV=3390;
> PROC SQL;
>      INSERT INTO SASDB.JOBINIT
>             SELECT * FROM TAPEDB.JOBINIT
>      ;
>      SELECT MIN(INIT_DATE_WRITTEN) FORMAT=YYMMDD10. ,
>             MAX(INIT_DATE_WRITTEN) FORMAT=YYMMDD10.
>      FROM SASDB.JOBINIT
>      ;
> ENDSAS;
> PROC APPEND OUT=SASDB.JOBINIT NEW=TAPEDB.JOBINIT;
> /*
>
> In both cases, the job simply goes into an infinite loop (well, I let it
> run
> for 27 hours with over 13 million EXCPs before I cancelled it one time!).
> This is a very large SAS database. Unfortunately, I am in the process of
> recreating it (it takes about 6 hours!). As I recall, the database ends up
> being about 50,000 tracks on 3 volumes (3390-3).
>
> Has anybody heard of a problem with SAS 8.0 with doing this? I don't know
> how to search for SAS "fixes". Thanks.
>
> --
> John McKown
> Applications and Solutions Team
> Healthaxis