dfsort

Concat Column2 data in one record based on column1 through JCL


I have a file having parent and child data in column1 and column2 respectively. I have a requirement to concat all the child from same parent in one record. How can we achive this through JCL ?

Expected Result shoold be like this : Input file :

Parent    |Child  
ABCDEFAAAA|1233444111  
ABCWEEAAAA|3456544111  
ABCDEFAAAA|3435677111  
HFFDDDAAAA|6444554111  
ABCDEFAAAA|2424234111  
HFFDDDAAAA|4334456111  

Output should be like this

Parent    |Child  
ABCDEFAAAA|1233444111,3435677111,2424234111  
ABCWEEAAAA|3456544111  
HFFDDDAAAA|6444554111,4334456111  

Solution

  • As Martin packer pointed out, it can be done in a single pass of data and get the desired results. Here is a sample which can handle upto 99 children per parent, but only the first 10 are handled as OP mentioned that the max is only 8.

    //STEP0100 EXEC PGM=SORT                       
    //SYSOUT   DD SYSOUT=*                         
    //SORTIN   DD *                                
    ABCDEFAAAA|1233444111                          
    ABCWEEAAAA|3456544111                          
    ABCDEFAAAA|3435677111                          
    HFFDDDAAAA|6444554111                          
    ABCDEFAAAA|2424234111                          
    HFFDDDAAAA|4334456111                          
    //SORTOUT  DD SYSOUT=*                         
    //SYSIN    DD *                                
      SORT FIELDS=(01,10,CH,A),EQUALS              
                                                   
      OUTREC IFTHEN=(WHEN=GROUP,                   
                 KEYBEGIN=(001,10),                
                     PUSH=(081:ID=8,               
                           090:SEQ=2)),            
                                                   
             IFTHEN=(WHEN=GROUP,                   
                    BEGIN=(090,2,ZD,EQ,01),        
                     PUSH=(094:12,10)),            
                                                   
             IFTHEN=(WHEN=GROUP,                   
                    BEGIN=(090,2,ZD,EQ,02),        
                      END=(090,2,ZD,EQ,01),        
                     PUSH=(105:12,10)),            
                                                   
             IFTHEN=(WHEN=GROUP,                   
                    BEGIN=(090,2,ZD,EQ,03),        
                      END=(090,2,ZD,EQ,01),        
                     PUSH=(116:12,10)),            
                                                   
             IFTHEN=(WHEN=GROUP,                   
                    BEGIN=(090,2,ZD,EQ,04),        
                      END=(090,2,ZD,EQ,01),        
                     PUSH=(127:12,10)),            
                                                   
             IFTHEN=(WHEN=GROUP,                   
                    BEGIN=(090,2,ZD,EQ,05),        
                      END=(090,2,ZD,EQ,01),        
                     PUSH=(138:12,10)),            
    
             IFTHEN=(WHEN=GROUP,                                         
                    BEGIN=(090,2,ZD,EQ,06),                              
                      END=(090,2,ZD,EQ,01),                              
                     PUSH=(149:12,10)),                                  
                                                                         
             IFTHEN=(WHEN=GROUP,                                         
                    BEGIN=(090,2,ZD,EQ,07),                              
                      END=(090,2,ZD,EQ,01),                              
                     PUSH=(160:12,10)),                                  
                                                                         
             IFTHEN=(WHEN=GROUP,                                         
                    BEGIN=(090,2,ZD,EQ,08),                              
                      END=(090,2,ZD,EQ,01),                              
                     PUSH=(171:12,10)),                                  
                                                                         
             IFTHEN=(WHEN=GROUP,                                         
                    BEGIN=(090,2,ZD,EQ,09),                              
                      END=(090,2,ZD,EQ,01),                              
                     PUSH=(182:12,10)),                                  
                                                                         
             IFTHEN=(WHEN=GROUP,                                         
                    BEGIN=(090,2,ZD,EQ,10),                              
                      END=(090,2,ZD,EQ,01),                              
                     PUSH=(193:12,10)),                                  
                                                                         
             IFTHEN=(WHEN=(090,2,ZD,EQ,01),                              
                  OVERLAY=(105:99X)),                                    
                                                                         
             IFTHEN=(WHEN=NONE,                                          
                  OVERLAY=(094:94,110,SQZ=(SHIFT=LEFT,MID=C',')))        
                                                     
      OUTFIL REMOVECC,NODETAIL,                                          
      SECTIONS=(81,8,                                                    
      TRAILER3=(01,12,94,110))                                           
    /* 
    

    The output of this is

    ABCDEFAAAA|21233444111,3435677111,2424234111       
    ABCWEEAAAA|33456544111                             
    HFFDDDAAAA|46444554111,4334456111