Newsgroups: bit.listserv.sas-l
Date:         Fri, 21 Aug 1992 10:49:24 ECT
Reply-To:     Lary Jones <LJONES@BINGVMB.BITNET>
From:         Lary Jones <LJONES@BINGVMB.BITNET>
Subject:      Sorting large files

Topic: Creating a sorted SAS dataset for a large sequential file
OpSys: IBM MVS ESA
Versn: SAS 6.07 - Synchsort callable via PROC SORT
From:  Lary Jones
Addr:  LJones@BingVMB.Bitnet, Binghamton University
       School of Management Research Support

  +------------------------------------+
  | WARNING: Includes very long example|
  +------------------------------------+

Recently thaere has been much discussion of handling large files in
SAS. Specifically, the allocation of work space can be a serious
problem, especially when one wishes to build a dataset sorted by key
variable(s). The example which follows is a real SAS job.  Yes, I
actually used it recently to create a SAS version of the SEC's
Ownership Reporting System (insider trading) data which was sorted by
CUSIP (company id code), person, and date.  (The ORS series of files
is available through the Electronic Records Division, National
Archives, Washington, DC 20408).

The tactics included involve:

1. Setting the size of the WORK space allocation to 20000 primary, and
   5000 secondary, blocks of 6144;

2. Increasing the number of sort work files to 5.  Often, individuals
   will see a SAS job blow out during a PROC SORT, and increase the
   allocation for the SAS WORK library.  This may not be necessary
   at all.  SAS will dynamically allocate SORT work files.  Increasing
   the number from 3 to 5 often helps (I think 6 is the max);

3. Judicious use of the LENGTH statement.  I checked the data, and knew
   that a length of 5 bytes would handle the largest numeric variable;

4. Divide and conquer.  The file is too big to sort in its entirety.
   I divided the file _during the first pass using the primary key_
   into 6 files, each of which is quite manageable;

5. Creating the (combined) output dataset on tape, thus not permanently
   tying up valuable disk space.  Not that the sorted components can
   be _sequentially_ added to the tape dataset.

I am sure that the true SAS wizards might improve on my efforts.  I
present the following log for discussion.  (Points above can be found
by looking for the string <== NOTE: #n)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          //*========================
        2 //  EXEC SAS,WORK='20000,5000'                <== NOTE: #1
       18 //IN1   DD  DSN=SYSDB.ORS.CUM.MAR91,DISP=OLD
          //*
       19 //ORS91 DD DSN=SOM.LJONES.ORS91.SASLIB,DISP=OLD,UNIT=TAPE80
       20 //SYSIN     DD *               GENERATED STATEMENT
       21 //
1                                                          The SAS System
10:41 Wednesday, August 19, 1992

NOTE: Copyright(c) 1989 by SAS Institute Inc., Cary, NC USA.
NOTE: SAS (r) Proprietary Software Release 6.07  TS301
      Licensed to STATE UNIVERSITY OF NEW YORK AT BINGHAMTON, Site 0009824001.

NOTE: Running on IBM Model 9021 Serial Number 171718.


NOTE: SAS system options specified are:
      VECTOR

NOTE: The initialization phase used 0.12 CPU seconds and 2896K.
1
2          OPTIONS NOCENTER LS=79 SortWkNo=5;           <== NOTE: #2
3
4          Data zz (DROP=bad_cnt)
5               i1 (DROP=bad_cnt)
6               i2 (DROP=bad_cnt)
7               i3 (DROP=bad_cnt)
8               i4 (DROP=bad_cnt)
9               i5 (DROP=bad_cnt);
10           LENGTH _Numeric_ 5  DEFAULT=4;            <== NOTE: #3
11           RETAIN bad_cnt 0;
12           infile in1;
13           input insider $ 1-7 code $ 8-9     nature $ 10     date 11-16
14              cusip $ 17-22    type2 $1.      shares 24-31    price 32-39
15              holdings 40-47   daterec 48-53  amendmen $ 54   late $ 55
16              Inconsis $ 56    filernam $ 57-86  relation $ 94-95
17              Issue $ 107-156  report 168-171;
18
2 The SAS System
                                               10:41 Wednesday, August 19, 1992

19           IF (Insider > ' ') AND (860101 <= Date <= 910410)
20              THEN SELECT;                 <== NOTE: #4
21                WHEN ('ZZ0000' < Cusip <= 'ZZ9999')  OUTPUT zz ;
22                WHEN ('000000' < Cusip <= '200000')  OUTPUT i1 ;
23                WHEN ('200000' < Cusip <= '400000')  OUTPUT i2 ;
24                WHEN ('400000' < Cusip <= '600000')  OUTPUT i3 ;
25                WHEN ('600000' < Cusip <= '800000')  OUTPUT i4 ;
26                WHEN ('800000' < Cusip <= '999999')  OUTPUT i5 ;
27                OTHERWISE DO;
28                  bad_cnt+1;
29                  IF MOD(bad_cnt,10) = 1 THEN
30                     PUT '  ?? Bad CUSIP value: ' _n_= bad_cnt= cusip=;
31                  END;
32                END;
33              ;run;

NOTE: The infile IN1 is:
      Dsname=SYSDB.ORS.CUM.MAR91,
      Unit=3480,Volume=015865,Disp=OLD,Blksize=32375,
      Lrecl=175,Recfm=FB

    ... warnings about bad data fields deleted ...

  ?? Bad CUSIP value: _N_=489640 BAD_CNT=11 CUSIP=000000
  ?? Bad CUSIP value: _N_=497586 BAD_CNT=21 CUSIP=000000
  ?? Bad CUSIP value: _N_=507111 BAD_CNT=31 CUSIP=000000

    ... additional warnings about bad CUSIPS deleted ...

  ?? Bad CUSIP value: _N_=928381 BAD_CNT=521 CUSIP=000000
  ?? Bad CUSIP value: _N_=928842 BAD_CNT=531 CUSIP=000000
  ?? Bad CUSIP value: _N_=930693 BAD_CNT=541 CUSIP=000000

NOTE: 949346 records were read from the infile IN1.
NOTE: The data set WORK.ZZ has 13456 observations and 17 variables.
NOTE: The data set WORK.I1 has 192871 observations and 17 variables.
NOTE: The data set WORK.I2 has 196550 observations and 17 variables.
NOTE: The data set WORK.I3 has 175631 observations and 17 variables.
NOTE: The data set WORK.I4 has 167341 observations and 17 variables.
NOTE: The data set WORK.I5 has 199629 observations and 17 variables.
NOTE: The DATA statement used 89.97 CPU seconds and 3930K.

34
35         proc datasets;
-----Directory-----

Libref:                  WORK
Engine:                  V607
Physical Name:           SYS92232.T104145.RA000.LCJORS.R0001245
Unit:                    DISK
Volume:                  PUB00A
Disposition:             NEW
Device:                  3380
Blocksize:               6144
4 The SAS System
                                               10:41 Wednesday, August 19, 1992

Blocks per Track:        7
Total Library Blocks:    20055
Total Used Blocks:       20031
Total Free Blocks:       24
Highest Used Block:      20045
Highest Formatted Block: 20048
Members:                 6

#  Name  Memtype  Indexes
-------------------------
1  I1    DATA
2  I2    DATA
3  I3    DATA
4  I4    DATA
5  I5    DATA
6  ZZ    DATA
36              contents data=work.i1;
37              ;run;

38

NOTE: The PROCEDURE DATASETS printed page 1.
NOTE: The PROCEDURE DATASETS used 0.05 CPU seconds and 4144K.

39         proc sort data=zz leave=50000 list message;
40              by cusip insider date;
41              ;run;

NOTE: Option LIST is ignored by SAS sort.
NOTE: Option MESSAGE is ignored by SAS sort.
NOTE: The data set WORK.ZZ has 13456 observations and 17 variables.
NOTE: The PROCEDURE SORT used 0.43 CPU seconds and 6176K.

42
43         proc sort data=i1 leave=50000 list message;
44              by cusip insider date;
45              ;run;

NOTE: 1575 blocks of size 6174 dynamically allocated on WORK for each of 5
      sort work data sets.
NOTE: The data set WORK.I1 has 192871 observations and 17 variables.
NOTE: The PROCEDURE SORT used 6.98 CPU seconds and 6176K.

46         proc sort data=i2 leave=50000 list message;
47              by cusip insider date;
48              ;run;

NOTE: 1605 blocks of size 6174 dynamically allocated on WORK for each of 5
      sort work data sets.
NOTE: The data set WORK.I2 has 196550 observations and 17 variables.
NOTE: The PROCEDURE SORT used 8.01 CPU seconds and 6176K.

49         proc sort data=i3 leave=50000 list message;
50              by cusip insider date;
51              ;run;

NOTE: The data set WORK.I3 has 175631 observations and 17 variables.
5 The SAS System
                                               10:41 Wednesday, August 19, 1992

NOTE: The PROCEDURE SORT used 6.20 CPU seconds and 6176K.

52         proc sort data=i4 leave=50000 list message;
53              by cusip insider date;
54              ;run;

NOTE: The data set WORK.I4 has 167341 observations and 17 variables.
NOTE: The PROCEDURE SORT used 7.21 CPU seconds and 6176K.

55         proc sort data=i5 leave=50000 list message;
56              by cusip insider date;
57              ;run;

NOTE: 1630 blocks of size 6174 dynamically allocated on WORK for each of 5
      sort work data sets.
NOTE: The data set WORK.I5 has 199629 observations and 17 variables.
NOTE: The PROCEDURE SORT used 7.31 CPU seconds and 6176K.

58
59         proc datasets;
-----Directory-----

Libref:                  WORK
Engine:                  V607
Physical Name:           SYS92232.T104145.RA000.LCJORS.R0001245
Unit:                    DISK
Volume:                  PUB00A
Disposition:             NEW
Device:                  3380
Blocksize:               6144
Blocks per Track:        7
Total Library Blocks:    25095
Total Used Blocks:       20031
Total Free Blocks:       5062
Highest Used Block:      24151
Highest Formatted Block: 24157
Members:                 6

#  Name  Memtype  Indexes
-------------------------
1  I1    DATA
2  I2    DATA
3  I3    DATA
4  I4    DATA
5  I5    DATA
6  ZZ    DATA
60              ;run;

61

NOTE: The PROCEDURE DATASETS used 0.02 CPU seconds and 6176K.

62         DATA  ORS91.CUM;            <== NOTE: #5
63           SET zz i1 i2 i3 i4 i5;
64          ;RUN;

NOTE: The data set ORS91.CUM has 945478 observations and 17 variables.
6 The SAS System
                                               10:41 Wednesday, August 19, 1992

NOTE: The DATA statement used 19.90 CPU seconds and 6208K.

65
66         proc datasets;
-----Directory-----

Libref:                  WORK
Engine:                  V607
Physical Name:           SYS92232.T104145.RA000.LCJORS.R0001245
Unit:                    DISK
Volume:                  PUB00A
Disposition:             NEW
Device:                  3380
Blocksize:               6144
Blocks per Track:        7
Total Library Blocks:    25095
Total Used Blocks:       20031
Total Free Blocks:       5062
Highest Used Block:      24151
Highest Formatted Block: 24157
Members:                 6

#  Name  Memtype  Indexes
-------------------------
1  I1    DATA
2  I2    DATA
3  I3    DATA
4  I4    DATA
5  I5    DATA
6  ZZ    DATA
66                         delete zz i1 i2 i3 i4 i5;
67

NOTE: Deleting WORK.ZZ (memtype=DATA).
NOTE: Deleting WORK.I1 (memtype=DATA).
NOTE: Deleting WORK.I2 (memtype=DATA).
NOTE: Deleting WORK.I3 (memtype=DATA).
NOTE: Deleting WORK.I4 (memtype=DATA).
NOTE: Deleting WORK.I5 (memtype=DATA).
NOTE: The PROCEDURE DATASETS used 0.24 CPU seconds and 6208K.

68         proc print data=ORS91.CUM (OBS=200);
69          ;RUN;

NOTE: The PROCEDURE PRINT printed pages 2-13.
NOTE: The PROCEDURE PRINT used 0.15 CPU seconds and 6259K.

70
71         proc means data=ORS91.CUM;
72          ;RUN;

NOTE: The PROCEDURE MEANS printed page 14.
NOTE: The PROCEDURE MEANS used 40.14 CPU seconds and 6389K.