IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sum similar fields
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York
PostPosted: Fri Mar 19, 2010 1:55 pm
 Reply with quote
I want to sum up similar records.
Below is the screen shot of my input file (LRECL 70), Could you please help me in this.
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
10990172009 GLOBAL OPS                              2332
10990240004 GLOBAL OPS                              59 
10990247009 SEC OPS                                 33 
10990880007 SEC OPS                                 2   
10990893000 SEC OPS                                 27221
10991019001 SEC OPS                                 752 
10991106006 SEC OPS                                 85 
10991108002 GLOBAL OPS                              661 
10991131004 SEC OPS                                 60 
10991139007 GLOBAL OPS                              77 
10991206004 SEC OPS                                 52 
10991241001 SEC OPS                                 56 

My output should look like below.
Output:
Code:
GLOBAL OPS   3129
SEC OPS      28271


I have searched the forum but i was helpless with my search... Please help...
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Fri Mar 19, 2010 2:30 pm
 Reply with quote
Hi Bhargav,
Check if below sort step works fine for you...
Note output sum is not left alligned. Is it ok for you? Or you need it to be left alligned?
Code:

//S1    EXEC  PGM=SORT                                       
//SYSOUT    DD  SYSOUT=*                                     
//SORTIN DD *                                               
10990172009 GLOBAL OPS                              2332     
10990240004 GLOBAL OPS                              59       
10990247009 SEC OPS                                 33       
10990880007 SEC OPS                                 2       
10990893000 SEC OPS                                 27221   
10991019001 SEC OPS                                 752     
10991106006 SEC OPS                                 85       
10991108002 GLOBAL OPS                              661     
10991131004 SEC OPS                                 60       
10991139007 GLOBAL OPS                              77       
10991206004 SEC OPS                                 52       
10991241001 SEC OPS                                 56       
//SORTOUT DD SYSOUT=*                                       
//SYSIN    DD    *                                           
  SORT FIELDS=(13,15,CH,A)                                         
  OUTFIL REMOVECC,NODETAIL,                                       
  SECTIONS=(13,15,TRAILER3=(13,15,TOT=(53,8,UFF,M10,LENGTH=8)))   
//*                                                               

Output will be
Code:

GLOBAL OPS         3129   
SEC OPS           28261   
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York
PostPosted: Fri Mar 19, 2010 2:54 pm
 Reply with quote
This worked perfectly!! Thanks alot...
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Fri Mar 19, 2010 3:03 pm
 Reply with quote
Bhargav_1058 wrote:
This worked perfectly!! Thanks alot...

Welcome... icon_smile.gif

BTW
Bhargav wrote:
SEC OPS 28271

but actually it is
DFSORT STEP wrote:
SEC OPS 28261
icon_wink.gif
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York
PostPosted: Sat Mar 20, 2010 11:39 am
 Reply with quote
Could you please help me out in calculating the percentage as well??

Input file (LRECL=80):
Code:
----+----1----+----2----+----3----+----4----+----5----
AAB EMEA                                          3052
AAF EMEA                                           315
AAJ EMEA                                          6796
AAL EMEA                                          6317
AAX EMEA                                            26
AEB PWM-23243565655567 787889999001212345455      2996
BCU UAS-345DFVDFD23232 32544545465767GFHGH55        73
CAR UAS-345DFVDFD23232 32544545465767GFHGH55     51724
CIB CAS-23243565655567 787889999001212345455     49587
COG GBL OPS                                       2931
COG SEC OPS                                     278207
COG UAS-345DFVDFD23232 32544545465767GFHGH55       168
DRS DIST                                          6576
ICG PWM-23243565655567 787889999001212345455      1981
IIS UAS-345DFVDFD23232 32544545465767GFHGH55        13


Out put (LRECL=80):
Code:
AAB EMEA                                          3052
AAF EMEA                                           315
AAJ EMEA                                          6796
AAL EMEA                                          6317
AAX EMEA                                            26
AEB PWM-23243565655567 787889999001212345455      2996
BCU UAS-345DFVDFD23232 32544545465767GFHGH55        73
CAR UAS-345DFVDFD23232 32544545465767GFHGH55     51724
COG UAS-345DFVDFD23232 32544545465767GFHGH55       168
DRS DIST                                          6576
ICG PWM-23243565655567 787889999001212345455      1981
IIS UAS-345DFVDFD23232 32544545465767GFHGH55        13
TOTAL                                            80037

EMEA                                             16506   20.62296188
PWM-23243565655567 787889999001212345455          4977   6.218374002
UAS-345DFVDFD23232 32544545465767GFHGH55         51978   64.94246411
DIST                                              6576   8.216200007
Total percentage                                         100


Below is example for Caculating percentage for EMEA group.
sum of all EMEA *100/TOTAL

Any help would be greatly Appreciated!!
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Mon Mar 22, 2010 11:47 am
 Reply with quote
Bhargav wrote:
Could you please help me out in calculating the percentage as well??

Hey hey hey.. it is not just calculation of percentage on top of solution provided before...

Input and Output you have shown before and now is having lot of differences.... icon_rolleyes.gif
1:key positions are changed
2:Output you have shown is also having all input records also.....
3:allignment of figures in input has been changed. Now it is right alligned.

Just my understanding.....
Do you want output as
Code:
EMEA                                             16506   20.62296188
PWM-23243565655567 787889999001212345455          4977   6.218374002
UAS-345DFVDFD23232 32544545465767GFHGH55         51978   64.94246411
DIST                                              6576   8.216200007
Total percentage                                         100

When input is
Code:
AAB EMEA                                          3052
AAF EMEA                                           315
AAJ EMEA                                          6796
AAL EMEA                                          6317
AAX EMEA                                            26
AEB PWM-23243565655567 787889999001212345455      2996
BCU UAS-345DFVDFD23232 32544545465767GFHGH55        73
CAR UAS-345DFVDFD23232 32544545465767GFHGH55     51724
CIB CAS-23243565655567 787889999001212345455     49587
COG GBL OPS                                       2931
COG SEC OPS                                     278207
COG UAS-345DFVDFD23232 32544545465767GFHGH55       168
DRS DIST                                          6576
ICG PWM-23243565655567 787889999001212345455      1981
IIS UAS-345DFVDFD23232 32544545465767GFHGH55        13

Please confirm..
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon Mar 22, 2010 8:56 pm
 Reply with quote
Hi Sambhaji,

Quote:
Hey hey hey.. it is not just calculation of percentage on top of solution provided before...

Input and Output you have shown before and now is having lot of differences....
In some places, this is known as "scope creep". . . icon_wink.gif

It is why i very seldom ever quote a "fixed price". . .
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York
PostPosted: Tue Mar 23, 2010 12:11 pm
 Reply with quote
Sambhaji wrote:
Bhargav wrote:
Could you please help me out in calculating the percentage as well??

Hey hey hey.. it is not just calculation of percentage on top of solution provided before...

Input and Output you have shown before and now is having lot of differences.... icon_rolleyes.gif
1:key positions are changed
2:Output you have shown is also having all input records also.....
3:allignment of figures in input has been changed. Now it is right alligned.

Just my understanding.....
Do you want output as
Code:
EMEA                                             16506   20.62296188
PWM-23243565655567 787889999001212345455          4977   6.218374002
UAS-345DFVDFD23232 32544545465767GFHGH55         51978   64.94246411
DIST                                              6576   8.216200007
Total percentage                                         100

When input is
Code:
AAB EMEA                                          3052
AAF EMEA                                           315
AAJ EMEA                                          6796
AAL EMEA                                          6317
AAX EMEA                                            26
AEB PWM-23243565655567 787889999001212345455      2996
BCU UAS-345DFVDFD23232 32544545465767GFHGH55        73
CAR UAS-345DFVDFD23232 32544545465767GFHGH55     51724
CIB CAS-23243565655567 787889999001212345455     49587
COG GBL OPS                                       2931
COG SEC OPS                                     278207
COG UAS-345DFVDFD23232 32544545465767GFHGH55       168
DRS DIST                                          6576
ICG PWM-23243565655567 787889999001212345455      1981
IIS UAS-345DFVDFD23232 32544545465767GFHGH55        13

Please confirm..


Hi Sambhaji,

The solution provided by you before is perfect!!
As a next step i want to calculate the percentage with this input file... Could you please help me in this?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Tue Mar 23, 2010 3:42 pm
 Reply with quote
Below sortsteps will give you desired output
Code:

//S1    EXEC  PGM=SORT                                             
//SYSOUT    DD  SYSOUT=*                                           
//SORTIN DD DSN=yourinputhere                                                       
//SORTOUT DD DSN=&TEMP,DISP=(NEW,PASS),LRECL=80,RECFM=FB           
//SYSIN   DD *                                                     
  SORT FIELDS=COPY                                               
  OUTFIL REMOVECC,NODETAIL,                                     
  TRAILER1=('GTOT,C''',TOT=(47,8,UFF,M11,LENGTH=8),C'''')       
/*                                                               
//S2    EXEC  PGM=SORT                                           
//SYSOUT    DD  SYSOUT=*                                         
//SORTIN DD DSN=yourinputhere
//SORTOUT DD SYSOUT=*                                             
//SYMNAMES DD DSN=&TEMP,DISP=SHR                                 
//SYSIN   DD *                                                   
  SORT FIELDS=(5,40,CH,A)                                         
  SUM FIELDS=(47,8,ZD)                                           
  OUTREC BUILD=(5,40,47,8,ZD,EDIT=(IIIIIIIT),GTOT)               
  OUTFIL BUILD=(1,40,X,41,8,X,                                   
            (+1000000,MUL,41,8,ZD),DIV,49,8,ZD,EDIT=(TT.TTTT),80:X),   
  TRAILER1=('TOTAL PERCENTAGE ',51:'100')                         
/*                                                               

Output will be
Code:

CAS-23243565655567 787889999001212345455    49587 12.0719       
DIST                                         6576 01.6009       
EMEA                                        16506 04.0183       
GBL OPS                                      2931 00.7135       
PWM-23243565655567 787889999001212345455     4977 01.2116       
SEC OPS                                    278207 67.7294       
UAS-345DFVDFD23232 32544545465767GFHGH55    51978 12.6540       
TOTAL PERCENTAGE                                  100           
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Tue Mar 23, 2010 3:56 pm
 Reply with quote
You can modify SYSIN for second step as
Code:

//SYSIN   DD *                                                         
  SORT FIELDS=(5,40,CH,A)                                               
  SUM FIELDS=(47,8,ZD)                                                 
  OUTFIL OVERLAY=(5,40,X,47,8,ZD,EDIT=(IIIIIIIT),X,GTOT,               
         51:(+1000000,MUL,42,8,ZD),DIV,51,8,ZD,EDIT=(TT.TTTT),23X),   
  TRAILER1=('TOTAL PERCENTAGE ',51:'100')                               
/*                                                                     
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
PostPosted: Wed Mar 24, 2010 11:37 am
 Reply with quote
Hi Sambhaji,

Why are you using +1000000,MUL and not +100,MUL ?

Please explain.

Thank you
Ajay
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York
PostPosted: Wed Mar 24, 2010 11:41 am
 Reply with quote
Hi Sambhaji,

Thank you very much for your help!!

It really works, you are rocking......
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Wed Mar 24, 2010 1:11 pm
 Reply with quote
dick scherrer wrote:
Hi Sambhaji,

Quote:
Hey hey hey.. it is not just calculation of percentage on top of solution provided before...

Input and Output you have shown before and now is having lot of differences....
In some places, this is known as "scope creep". . . icon_wink.gif

It is why i very seldom ever quote a "fixed price". . .


Yes Dick...hard to quote fixed bid for "i want something" kind of requirement

Ajay Baghel wrote:
Hi Sambhaji,

Why are you using +1000000,MUL and not +100,MUL ?

Please explain.

Thank you
Ajay


Lets take first record
Total value is 49587 and grand total which is calculated in SYMNAMES is 410762

49587*1000000 will be 49587000000
now
49587000000/410762 will be 120719 (DFSORT only keeps the integer part)
now we are editing 120719 as TT.TTTT which will give 12.0719

I hope it is clear....

Bhargav wrote:
Hi Sambhaji,

Thank you very much for your help!!

It really works, you are rocking......

Am glad it helped icon_smile.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Mar 24, 2010 10:29 pm
 Reply with quote
Bhargav_1058,

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), DFSORT now supports the JOINKEYS function which will give you the desired results in one step and calculating the accurate total percentage by summing all the percentages.


Code:

//STEP0100 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//DETAIL   DD DSN=your input file,DISP=SHR
//SUMMARY  DD DSN=your input file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD    *                                                     
  JOINKEYS F1=DETAIL,FIELDS=(81,1,A)                                   
  JOINKEYS F2=SUMMARY,FIELDS=(81,1,A)                                 
  REFORMAT FIELDS=(F1:5,50,F2:81,1,45,10)
                             
  SORT FIELDS=(1,40,CH,A)                                             
  SUM FIELDS=(41,10,ZD)                                               
  OUTREC OVERLAY=(65:(+1000000,MUL,41,10,ZD),DIV,52,10,ZD,             
                       EDIT=(TT.TTTT),80:X)                           

  OUTFIL REMOVECC,BUILD=(1,40,41,10,ZD,M10,LENGTH=10,X,65,7,80:X),     
  TRAILER1=(52:7C'*',/,                                               
            'TOTAL PERCENTAGE ',51:TOT=(65,7,UFF,EDIT=(IIT.TTTT)))     
//*                                                                   
//JNF1CNTL DD *                                                       
  INREC OVERLAY=(81:X)                                                 
//*                                                                   
//JNF2CNTL DD *                                                       
  INREC OVERLAY=(81:X)                                                 
  SUM FIELDS=(45,10,ZD)                                               
//*


The output from this job is
Code:

CAS-23243565655567 787889999001212345455     49587 12.0719
DIST                                          6576 01.6009
EMEA                                         16506 04.0183
GBL OPS                                       2931 00.7135
PWM-23243565655567 787889999001212345455      4977 01.2116
SEC OPS                                     278207 67.7294
UAS-345DFVDFD23232 32544545465767GFHGH55     51978 12.6540
                                                   *******
TOTAL PERCENTAGE                                   99.9996


For complete details on JOINKEYS and the other new functions available with the Nov, 2009 DFSORT PTF, see:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Concatenate 2 fields (usage national)... COBOL Programming 2
No new posts Cobol COMP-2 fields getting scrambled... Java & MQSeries 6
No new posts Converting unpacked fields to pack us... SYNCSORT 4
No new posts Data for newly added fields not displ... IMS DB/DC 6
This topic is locked: you cannot edit posts or make replies. SUM FIELDS=NONE in reverse - Get dupl... DFSORT/ICETOOL 9
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us