The LAST command is very useful when you need to capture information from the previous record on the data base. In the example below, the last command is used to identify the difference in base pay rate from the prior employment history transaction. Note: SSA is not displayed in the sample report below, but is a sort field in the report request.
NAME ----
| PSN --- | EFF DATE --------
| TRAN ---- | TYPE ---- | BASEPAY ------- | DIFF ----
|
ANT,GINA | 01 | 08/24/1987 01/01/1998 04/01/1998 08/22/1988
| A50 GEN 120 MSA | PERMANENT/AT WILL APPT GENERAL SALARY INCREASE POSITION NUMBER SERVICE-BASED SALARY INC | $5,376.00 $5,570.00
$5,570.00 $5,684.00
| $.00 $194.00 $.00
$114.00
|
The report request to generate this report is done into two parts. The first part sorts data in the TR file and places the data in a hold file. The second part calculates the differences in base pay using the LAST command and generates the final report
Part I
By default, transactions in the TR file are sorted in descending date order (high to low). Before the LAST command can be used, the transactions must first be sorted in ascending date order (low to high) using the command BY HIGHEST TR:LISTCNT. The field TR:LISTCNT identifies the correct posting order of the transactions. The command HIGHEST reverses the sort order.
EX TR
TABLE FILE TR
PRINT TR:EFFDATE TR:TRANCODE TR:TRANNAME TR:BASEPAY TR:WNAME
BY TR:SSA
BY TR:PSNSEQ
BY HIGHEST TR:LISTCNT
ON TABLE HOLD AS PERMSML
END
Part II
After the data is sorted in the correct order, the LAST command is used in a define (LASTBASE) to retrieve the base pay from the prior record only if the record is for the same SSN and within the same position sequence. After that value is determined, another field is defined (DIFF) to determine the difference between base pay on the current transaction and the field LASTBASE.
DEFINE FILE PERMSML ADD
LASTBASE/P12.2M = IF (TR:SSA EQ LAST TR:SSA) AND (TR:PSNSEQ EQ LAST TR:PSNSEQ)
THEN LAST TR:BASEPAY ELSE 0;
DIFF/P12.2M = IF LASTBASE GT 0 THEN (TR:BASEPAY - LASTBASE) ELSE 0;
END
TABLE FILE PERMSML
PRINT TR:EFFDATE AS 'EFF DATE'
TR:TRANCODE AS 'TRAN'
TR:TRANNAME/A25 AS 'TYPE'
TR:BASEPAY/P8.2M AS 'BASE'
DIFF AS 'DIFF'
BY TR:WNAME AS 'NAME' SKIP-LINE
BY TR:SSA AS 'SSA' FOLD-LINE
BY TR:PSNSEQ AS 'PSN'
BY HIGHEST TR:LISTCNT NOPRINT
END
The entire report request is available in the common library as LASTCMD.
Last Updated: February 27, 2024