如何在JCL中按Id分组时同时汇总Amount并获取最大Date

Got it, let's tackle your DFSORT requirement: you need to group records by the Id field, calculate the sum of Amount for each group, and also get the latest (maximum) Date value per group.

Input Data #

Id Amount Date
--------------------
123 200 20180516
123 300 20180520
456 100 20180616
456 700 20180420

Expected Result #

Id Amount Date
--------------------
123 500 20180520
456 800 20180616

Analysis of Your Current Code #

Your existing SORT step handles grouping by Id and summing Amount, but it doesn't account for capturing the maximum Date per group. The SUM FIELDS parameter only specifies the sum operation for the Amount field—we need to extend it to include the max date logic.

Corrected DFSORT JCL Code #

Here's the adjusted code that meets both your requirements:

SORTST5 EXEC PGM=SORT
SYSOUT   DD SYSOUT=*
SYSPRINT DD SYSOUT=*
SORTIN   DD DSN=&VNTMP..WRK.INWORK,DISP=SHR
SORTOUT  DD DSN=&VNTMP..WRK.OUTWORK.OUT.FRM,
            DISP=(,CATLG,DELETE),
            RECFM=FB,LRECL=84,
            SPACE=(CYL,(100,100),RLSE)
SYSIN    DD *
  SORT FIELDS=(1,3,PD,A)
  SUM FIELDS=(4,3,PD,+ , 8,8,CH,MAX)
/*

Key Explanations #

Let's break down the critical parts:

• SORT FIELDS=(1,3,PD,A):
  Sorts records by the Id field (positions 1-3, packed decimal type) in ascending order. This ensures all records with the same Id are grouped together for processing.
• SUM FIELDS=(4,3,PD,+ , 8,8,CH,MAX):
  • (4,3,PD,+): Performs a sum operation on the Amount field (positions 4-6, packed decimal type) for each Id group.
  • (8,8,CH,MAX): Retrieves the maximum value from the Date field (positions 8-15, character type). Since your date is in YYYYMMDD format, lexicographical order matches chronological order—so the "maximum" character value is the latest date.

Quick Notes #

• Double-check the field positions and data types to match your actual input record layout. If your Date field uses a different type (e.g., packed decimal), adjust the CH (character) specifier to PD accordingly.
• I fixed a typo in your SORTIN dataset name (&VNTMPççWRK.INWORK → &VNTMP..WRK.INWORK) to ensure proper dataset resolution.

内容的提问来源于stack exchange，提问作者debegris