MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

sum records with same group and form total of all groups

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
yadav2005
Intermediate


Joined: 10 Jan 2005
Posts: 348
Topics: 144
PostPosted: Wed Oct 22, 2008 12:22 pm    Post subject: sum records with same group and form total of all groups Reply with quote
I have a file with many records which are having department and salary values as below:
Code:

ABC   10
DEF    5
ABC   20
PQR   54
PQR   12
PQR  -90

I want the output in the format as below where in for each department values should be added up and finally also the total should be summed up
Code:

ABC   30
DEF    5
PQR  -24
     ---
TOT:  11

And i want another output in the format which should should display as below only for DEF & PQR the positive value should come in PLUS and negative value should come in MINUS and finally all the values should be added up and i do not want to do anything with ABC.Hope i am clear in my question.Basically i want two reports .
Code:

             PLUS     MINUS
ABC   30                   
DEF    5        5         
PQR  -24                -24
     ---     ----     -----
TOT:  11        5       -24
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Oct 22, 2008 1:10 pm    Post subject: Reply with quote
yadav2005,

The following DFSORT JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICEMAN                                             
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
ABC   10                                                               
DEF    5                                                               
ABC   20                                                               
PQR   54                                                               
PQR   12                                                               
PQR  -90                                                               
//RPT1     DD SYSOUT=*                                                 
//RPT2     DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,5,4,SFF,PD,LENGTH=4))             
  SORT FIELDS=(1,4,CH,A)                                               
  SUM FIELDS=(5,4,PD)                                                 
                                                                       
  OUTREC IFTHEN=(WHEN=INIT,                                           
  BUILD=(1,4,5,4,PD,EDIT=(SIIIT),SIGNS=(,-))),                         
  IFTHEN=(WHEN=(5,5,SFF,GT,0,AND,1,3,CH,NE,C'ABC'),                   
  OVERLAY=(11:5,5)),                                                   
  IFTHEN=(WHEN=(5,5,SFF,LT,0,AND,1,3,CH,NE,C'ABC'),                   
  OVERLAY=(17:5,5))                                                   
                                                                       
  OUTFIL FNAMES=RPT1,REMOVECC,BUILD=(1,10),                           
  TRAILER1=(5:5C'-',/,1:'TOT:',5:TOT=(5,5,SFF,EDIT=(SIIIT),SIGNS=(,-)))
                                                                       
  OUTFIL FNAMES=RPT2,REMOVECC,BUILD=(1,21),                           
  TRAILER1=(05:5C'-',11:5C'-',17:5C'-',/,1:'TOT:',                     
            05:TOT=(05,5,SFF,EDIT=(SIIIT),SIGNS=(,-)),                 
            11:TOT=(11,5,SFF,EDIT=(SIIIT),SIGNS=(,-)),                 
            17:TOT=(17,5,SFF,EDIT=(SIIIT),SIGNS=(,-)))                 
/*


RPT1 output is
Code:

ABC    30
DEF     5
PQR   -24
    -----
TOT:   11


RPT2 output is
Code:

ABC    30             
DEF     5     5       
PQR   -24         -24
    ----- ----- -----
TOT:   11     5   -24


Hope this helps..
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
yadav2005
Intermediate


Joined: 10 Jan 2005
Posts: 348
Topics: 144
PostPosted: Wed Oct 22, 2008 1:49 pm    Post subject: Reply with quote
Kolusu,

Thanks a lot for your reply and It works fine for me. Can you help me with a simple solution which will give the output for report 1 only , what should i take out from your code:
I have a file with many records which are having department and salary values as below:
Code:

ABC   10
DEF    5
ABC   20
PQR   54
PQR   12
PQR  -90

I want the output in the format as below where in for each department values should be added up and finally also the total should be summed up
Code:

ABC   30
DEF    5
PQR  -24
     ---
TOT:  11
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
PostPosted: Wed Oct 22, 2008 1:55 pm    Post subject: Reply with quote
Did you read the reply and understand it? Look at the OUTFIL FNAMES= lines
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Oct 22, 2008 2:28 pm    Post subject: Reply with quote
yadav2005,

*sigh* if you just need 1 report then you dont need the outrec and sum processing. Run the following DFSORT JCL.

Code:

//STEP0100 EXEC PGM=ICEMAN                                           
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
ABC   10                                                             
DEF    5                                                             
ABC   20                                                             
PQR   54                                                             
PQR   12                                                             
PQR  -90                                                             
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
 SORT FIELDS=(1,4,CH,A)                                               
 OUTFIL REMOVECC,NODETAIL,                                           
 SECTIONS=(1,4,                                                       
 TRAILER3=(1,4,5:TOT=(5,5,SFF,EDIT=(SIIIT),SIGNS=(,-)))),             
 TRAILER1=(5:5C'-',/,1:'TOT:',5:TOT=(5,5,SFF,EDIT=(SIIIT),SIGNS=(,-)))
/*


Hope this helps...

Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
yadav2005
Intermediate


Joined: 10 Jan 2005
Posts: 348
Topics: 144
PostPosted: Wed Oct 22, 2008 4:54 pm    Post subject: Reply with quote
Kolusu,

Thanks a lot once again , it helped me and it was a nice learning.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group