Newsgroups: bit.listserv.sas-l
Date:         Mon, 27 Jul 1992 10:43:29 EDT
Reply-To:     Howard Schreier <HIS@NIHCU.BITNET>
From:         Howard Schreier <HIS@NIHCU.BITNET>
Subject:      Combining Diverse Files

CONTENT:  Response
SUMMARY:  Use trailing "@" to read/write repeating fields

A user has to combine data from three differently  formatted
external files.  The details:

>  First:  Lrecl = 40
>          date (4.)     fid ($7.)   dummy (16.)   itm (5.)      qty (8.)
>             0101        a100001     123986....     45422        34400
>             0101        a100001          .         45666        23455
>              .            .              .             .           .
>             0131        a100001          .         00123        123234
>             0101        a100002          .         12993        123300
>              .           .               .            .            .
>
>  Second:  Lrecl = 110
>            date  fid  itm1  qty1  itm2  qty2  ... itm8  qty8   check
>                                                                  *
>           * the check # is the symbol of how many records belongs to
>             the same fid on the same date.
>
>  Third:   Lrecl = 341
>           fid  date  dummy (19.)  account (2.)  qty1 itm1 ... qty24 itm24
>                                      *                      *     *
>           * account is the symbol of the qty# and itm#.
>             eg:  account = 12  implied from qty1 itm1 to qty12 itm12
>
>   finally I want to manage the dataset as:
>
>       Lrecl = 80
>        fid   date             blank  (67)                       cd (2)
>        fid   date  itm1 (5)  qty1 (8) ... itm5  qty5  blank (2) cd (2)
>                                                                 *
>        * take the card # appart.
>          eg:  cd = 1        is the card included fid date
>               cd = 2 or 3 or ...    is the sequence # of the contant
>                fid itm qty
>               if the qty# & itm# over 5 on the same date in the same fid
>               then cd will be add 1.
>
>   Any advice is appreciated.   Welcome your anser.
> Daisy Hsu
> Bitnet : Daisy@twnmoe10

To make things more manageable, I  will  work  with  smaller
field widths (3 for FID, 4 for DUMMY, 1 for ITM, 2 for QTY),
fewer repetitions (3 for the second input file,  4  for  the
third), and a record length of 30 for the output file.

Reading the first input is straightforward.  The "+" pointer
control is used to jump over the DUMMY field.

   data sub1;
   input date 4. fid $3. +4 itm $1. qty 2.;
   cards;
   0101a10    A12
   1231z99    C21
   ;

The second input is messier,  because  some  fields  repeat.
The trailing "@" pointer control holds the input record.

   data sub2;
   input date 4. fid $3.    @;
   do across = 1 to 3;
      input itm $1. qty 2.  @;
      * Filter out blank fields;
      if itm ne ' ' then output;
      end;
   cards;
   0101a10G35A44L58 2
   0101a10E01       2
   1231z99N26       1
   ;

Note that the CHECK field is not used or even read.  Turning
to the third file, the trailing "@" is again useful.

   data sub3;
   input date 4. fid $3. +4 account 2. @;
   do across = 1 to account;
      input itm $1. qty 2.             @;
      output;
      end;
   cards;
   0427h18    04D28C27B26A25
   0427h18    02Y24X23
   0704k10    01W22
   ;

If the original files are in the desired sort sequence,  the
subsets  can  now  be  interleaved  and transcribed into the
specified  form   (otherwise,   there   are   two   options:
concatenate  the  subsets,  sort the combined data set, then
create the output; or  sort  the  subsets  separately,  then
interleave as shown).

   data _null_;
   set sub1 sub2 sub3; by fid date;
   file print;
   if first.date then do;
      cd = 1;
      * Write header;
      put fid $3. date z4. @29 cd z2.;
      end;
   field + 1;
   if field=1 then do;
      cd + 1;
      * Start new data record, position pointer for field #1, and hold;
      put fid $3. date z4. @29 cd z2. @8 @;
      end;
   put itm $1. qty 2.                    @;
   if field=5 or last.date then do;
      * Release record;
      put;
      field = 0;
      end;

The trailing "@" is again  useful,  this  time  for  output.
Also,  note  that  the  two  counters,  CD  and  FIELD,  are
automatically RETAINed  because  they  appear  in  summation
statements.

The results, combining the test data shown:

   a100101                     01
   a100101A12G35A44L58E 1      02
   h180427                     01
   h180427D28C27B26A25Y24      02
   h180427X23                  03
   k100704                     01
   k100704W22                  02
   z991231                     01
   z991231C21N26               02

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
\   Howard Schreier, U.S. Dept. of Commerce, Washington    /
/                     MVS 5.18 & 6.07                      \
\   Voice: (202) 377-4180        BITNET: HIS@NIHCU         /
/   Fax:   (202) 377-4614      INTERNET: HIS@CU.NIH.GOV    \
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU