Holding Data Using Across Command

The HOLD command lets you retrieve and process data, then hold the results for further processing.  The resulting data file contains the original fieldnames and values requested per verb (PRINT, SUM, COUNT, LIST) and sort (BY) commands, or generated using the ACROSS command.

 

For example, compare the report requests below.  Both reports are summing gross pay for individual employees by pay period, but one is using the sort BY and the other is using the sort ACROSS.  

 

Note:  The tables below are best viewed with the navigation pane hidden.

 

 Using BY​

Data Generated

Hold File MFD

EX PH

TABLE FILE PH

SUM PH:GROSSPAY

BY PH:SSA

BY PH:PAYPERIOD

ON TABLE HOLD AS PERMRPT

END

 

PH:SSA       PH:PAYPERIOD  PH:GROSSPAY

------       ------------   -----------

001-11-1111  2004/09          $2356.00

001-11-1111  2004/10          $2356.00

001-11-1111  2004/11          $2356.00

001-11-1111  2004/12          $2356.00

FIELDNAME     ALIAS  FORMAT

 

PH:SSA        E01    A11

PH:PAYPERIOD  E02    YYM

PH:GROSSPAY   E03    P12.2

 

Using ACROSS

Data Generated

Hold File MFD

EX PH

TABLE FILE PH

SUM PH:GROSSPAY

BY PH:SSA

ACROSS PH:PAYPERIOD

ON TABLE HOLD AS PERMRPT

END

 

PH:SSA       2004/09  2004/10  2004/11  2004/12

------       -------  -------  -------  -------

001-11-1111 $2356.00 $2356.00 $2356.00 $2356.00

FIELDNAME    ALIAS  FORMAT

 

PH:SSA       E01    A11

PH:200409    E02    P12.2

PH:200410    E03    P12.2

PH:200411    E04    P12.2

PH:200412    E05    P12.2

 

As you can see, the Master File Descriptions (MFD) generated for the hold files are very different.  In the second request, the resulting values for the field PH:PAYPERIOD have become individual data columns.  This technique is especially useful when you need to condense multiple rows of data for an individual into a single row of data.  

 

When generating reports from the hold file data, either the fieldname or the alias can be used in the request.  For example:to identify employee's who received pay for both the September and December pay periods:

 

TABLE FILE PERMRPT

PRINT PH:SSA

WHERE (PH:200409 GT 0) AND (PH:200412 GT 0)

END


Last Updated: April 10, 2024