Using DFSORT need to add column values

[Vikram321]

Hi, Can anyone plz. help me to find out the jcl for below req.

I have a input flat file which contains some record like

1st column: Main account
2nd: Collateral acct
3rd: CUSIP
4th : some values against CUSIP

I NEED AN OUTPUT FILE WHICH WILL HAVE SUMMED UP VALUES OF "AMOUNT" AGAINST EACH "COLLATERAL" ACCOUNT. IN THE 2ND COULUM WE CAN SEE THAT WE HAVE A "COLLTARAL" ACCOUNT 34567854 AND IT HAS 5 DIFFERENT "CUSIPS" TAGGED GAINST IT IN THE 3RD COLUMN AND IT HAS 5 DIFFERENT "AMOUNT" VALUES IN THE 4TH COLUMN. MY REQUIRMENT IS TO GET THE SUMMED UP VALUES OF ALL CUSIP "AMOUNT" TAGGED AGAINST A "COLLATERAL" ACCOUNT.

FOR EXAMPLE: COLLATERAL 34567854 WILL HAVE A TOTAL OF 15000 WHEN WE ADD ALL THE CUSIP AMOUNT TAGGED AGAINST IT.

Input file looks below:

Code: Select all

=COLS>	----+----1----+----2----+----3----+----4--
******	***************************** TOP OF DATA 
000001	 ACCOUNT COLATERAL CUSIP     AMOUNT       
000002	 12345678 34567854 09876556A 1000.00      
000003	                   89567456E 2000.00      
000004	                   89567456R 3000.00      
000005	                   89567456T 4000.00      
000006	                   89567456Y 5000.00      
000007	          89651235 89567455Q 7000.00      
000008	                   89567455W 1000.00      
000009	                   89567455E 2000.00      
000010	                   89567455R 3000.00      
000011	                   89567455T 1000.00      
000012	                   89567455Y 2000.00      
000013	 12389658 34512554 09878656A 1000.00      
000014	                   89567866E 2000.00      
000015	                   89586456R 3000.00      
000016	          89698735 89741455Q 4000.00      
000017	                   89698455W 5000.00      
000018	                   89852455E 7000.00      

BUT I NEED THE FILE IN BELOW FORMAT

summed up values of all amount in a group wise at collateral acct level
like

Code: Select all

12345678                   34567854       09876556A             15000.00
                           89651235       89567455Q             16000.00
12389658                   34512554       09878656A             60000.00
                           89698735       89741455Q             16000.00
 

Card that i huv used:

Code: Select all

INREC OVERLAY=(30:30,7,SFF,TO=ZD,LENGTH=7)                      
SORT FIELDS=(11,8,CH,A)                                         
SUM FIELDS=(30,7,ZD)                                            
OUTREC OVERLAY=(30:30,7,ZD,EDIT=(STTTTTTTTTTTTT.TT),SIGNS=(+,-))

USING ABOVE CODE I GOT BELOW OUTPUT FILE :

Code: Select all

                
=COLS>	----+----1----+----2----+----3----+----4----+----5
******	***************************** TOP OF DATA ********
000001	                   89567456E +0000000040000.00    
000002	 12389658 34512554 09878656A +0000000001000.00    
000003	 12345678 34567854 09876556A +0000000001000.00    
000004	          89651235 89567455Q +0000000007000.00    
000005	          89698735 89741455Q +0000000004000.00    

SYSOUT DETAILS:

Code: Select all

ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                          
ICE751I 0 C5-I12416 C6-K90026 C7-K94453 C8-K94453 E9-K60824 C9-BASE   E5-K80744 
ICE143I 0 BLOCKSET     SORT  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT HTTP://WWW.IBM.COM/STORAGE/DFSORT FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 06:44 ON THU AU
          *                                                                     
            INREC OVERLAY=(30:30,7,SFF,TO=ZD,LENGTH=7)                          
            SORT FIELDS=(11,8,CH,A)                                             
            SUM FIELDS=(30,7,ZD)                                                
            OUTREC OVERLAY=(30:30,7,ZD,EDIT=(STTTTTTTTTTTTT.TT),SIGNS=(+,-))    
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICETD1 ENVIRONMENT SELECTED 
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS         
ICE088I 1 RB681PCL.STEP06  .        , INPUT LRECL = 131, BLKSIZE = 27903, TYPE =
ICE093I 0 MAIN STORAGE = (MAX,16777216,16777216)                                
ICE156I 0 MAIN STORAGE ABOVE 16MB = (16732144,16719856)                         
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,
ICE128I 0 OPTIONS: SIZE=16777216,MAXLIM=1048576,MINLIM=262144,EQUALS=N,LIST=Y,ER
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=
ICE130I 0 OPTIONS: RESALL=12288,RESINV=0,SVC=109 ,CHECK=N,WRKREL=N,OUTREL=N,CKPT
ICE131I 0 OPTIONS: TMAXLIM=16777216,ARESALL=12288,ARESINV=0,OVERRGN=65536,CINV=Y
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITC
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMI
ICE235I 0 OPTIONS: NULLOUT=RC0                                                  
ICE236I 0 OPTIONS: DYNAPCT=100,MOWRK=Y                                          
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTOUT                                   
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN                                    
ICE084I 0 EXCP ACCESS METHOD USED FOR SORTIN                                   
ICE750I 0 DC 27903 TC 0 CS DSVNN KSZ 8 VSZ 8                                   
ICE752I 0 FSZ=213 RC  IGN=0 E  AVG=132 0  WSP=37 C  DYN=0 0                    
ICE751I 1 DE-K83743 D5-K91600 D9-K61787 E8-I12416                              
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 27920, TYPE = FB   (SDB)                
ICE171I 0 SORTOUT LRECL OF 80 IS DIFFERENT FROM SORTIN(NN) LRECL OF 131 - RC=0 
ICE080I 0 IN MAIN STORAGE SORT                                                 
ICE055I 0 INSERT 0, DELETE 12                                                  
ICE054I 0 RECORDS - IN: 17, OUT: 5                                             
ICE134I 0 NUMBER OF BYTES SORTED: 2227                                         
ICE253I 0 RECORDS SORTED - PROCESSED: 17, EXPECTED: 213                        
ICE199I 0 MEMORY OBJECT USED AS MAIN STORAGE = 0M BYTES                        
ICE299I 0 MEMORY OBJECT USED AS WORK STORAGE = 0M BYTES                        
ICE180I 0 HIPERSPACE STORAGE USED = 0K BYTES                                   
ICE188I 0 DATA SPACE STORAGE USED = 0K BYTES                                   
ICE052I 0 END OF DFSORT                                                        

[William Collins]

You see how much easier it is when you post, almost, everything?

If you look at your input, you'll see that the key you are sorting on only has data for the first item of a group of records. Same with the account. If you SORT that, you'll get all those spaces together, and added up, as you did and as we can see this time.

Your data is in sequence already, so SORTing is a waste of resources anyway.

You need to use IFTHEN=(WHEN=GROUP to PUSH the main account number, and a second one to do that for the collateral.

If determined to stick to SUM, you should use MERGE with a single input DD.

The alternative is OUTFIL reporting functions, using REMOVECC,NODETAIL and SECTIONS with TRAILER3.

Either way, you should include your Main Account in the key to stop even the potential of a false hit (with your existing Control Cards, you've scattered the data across your output file by SORTing on your collateral, you just don't know it).

[Vikram321]

Thanks for the response. Can you show us an example code of doing that.

[William Collins]

There are examples in the documentation. Look up MERGE and the examples there, look up OUTFIL and SECTIONS, remembering to take note of REMOVECC,NODETAIL and TRAILER3. Look at relevant examples.

[Kaku]

Example 14. Sort with IFTHEN

and for REMOVECC and other parameters this should help.