6. Database Tailoring and Retrofitting Techniques › 6.3 Retrofitting Guidelines › 6.3.2 Retrofitting Database Files › 6.3.2.2 Value Change With Order Change

6.3.2.2 Value Change With Order Change

Assume that there is still a one-to-one mapping of the old
key data element value to the new value.  That is, every old
key value translates to one and ONLY one new key value.
However, in contrast to the last case, either the values will
not take the same order as the old values, or it cannot be
determined that the new values will take the same order as
the old.  For example, here is a sample table showing old and
new key data element values and file observation order:

    Old     Old     New     New
    Value   Order   Value   Order
    -----   -----   -----   -----
    AA01    1       AB01    2
    AA02    2       AB02    3
    BA01    3       AA01    1

Not only must the data values be corrected, but the file must
be re-sorted.  The template for such a generalized recovery
job becomes:

    DATA WORK.iiifffnn;
      SET  &diiit..iiifffnn;
      (correction logic)
    RUN;
    PROC SORT DATA=WORK.iiifffnn
        OUT=&diiit..iiifffnn(%fffFILE(OP=FILEOPTS,TS=sssss));
      BY %fffSEQ(TS=sssss); RUN;

where d     is the database unit identifier
      iii   is the information area identifier
      t     is the timespan identifier
      fff   is the file identifier
      nn    is the file cycle number
      sssss is the timespan name

Putting the macro treatment method into practice can be done
by a method similar to that used in the previous section.  A
template for this method is:

   //xxx JOB (your job card)
   //STEP1 EXEC MICSDBd
   //SYSIN DD *
    %INCLUDE USOURCE($BASMSTR);
   %MACRO RETFIT(DDN,III,FFF,CYC,TSPAN);
      %LOCAL FILEID;
      %LET FILEID = &III&FFF&CYC;
      DATA WORK.&FILEID (KEEP=
                          %&FFF.KEEP(TS=&TSPAN)
                         );;;;
      SET  &DDN..&FILEID;
      (correction logic)
    
    RUN;
    PROC SORT DATA=WORK.&FILEID
       OUT=&DDN..&FILEID(%&FFF.FILE(OP=FILEOPTS,TS=&TSPAN));
      BY %&FFF.SEQ(TS=&TSPAN); RUN;
    %MEND RETFIT;
     /* RETROFIT DETAIL FILES */
    %RETFIT(&diiiX,iii,fff,nn,DETAIL); /*RETROFIT 1ST CYCLE*/
     ...
    %RETFIT(&diiiX,iii,fff,nn,DETAIL); /*RETROFIT LAST CYCL*/
     /* RETROFIT MONTHS FILES */
    %RETFIT(&diiiM,iii,fff,nn,MONTHS); /*RETROFIT 1ST CYCLE*/
     ...
    %RETFIT(&diiiM,iii,fff,nn,MONTHS); /*RETROFIT LAST CYCL*/

Again, this database retrofit should be done at the same
time as permanent corrections to CA MICS logic or user exit
code.  This assures that a single form of the key element
value exists at a given time.