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 

Need sum of a column and number of columns.

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


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Wed Sep 19, 2007 3:23 am    Post subject: Need sum of a column and number of columns. Reply with quote
Hi,

I have a FB file of rec length 50. The sample records are given below. Now I need a JCl to give the sum of the field in bold and the count of number of records. I can use DFsort and SYNCsort only.

234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22

thanks,
Suresh.
Back to top
View user's profile Send private message
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Wed Sep 19, 2007 4:02 am    Post subject: Re: Need sum of a column and number of columns. Reply with quote
Suresh,

You can use the below JCL to find out total # of records.

Code:
//COUNT    EXEC PGM=ICETOOL                       
//TOOLMSG   DD SYSOUT=*                             
//DFSMSG    DD SYSOUT=*                             
//IN        DD DSN=INPUT DATASET,
//             DISP=SHR                             
//TOOLIN    DD *                                   
  COUNT FROM(IN)                                   
/*


For adding column values you can use the below JCL.


Code:
//STEP0100 EXEC PGM=SORT                         
//SYSOUT   DD SYSOUT=*                           
//SORTIN   DD *                                   
234526.05.2007 0123457.11 0012345.22             
234526.05.2007 0123457.11 0012345.22             
234526.05.2007 0123457.11 0012345.22             
234526.05.2007 0123457.11 0012345.22             
//SORTOUT  DD SYSOUT=*                           
//SYSIN    DD *                                   
  SORT FIELDS=COPY                               
  OUTREC FIELDS=(01,80,                           
                 27,07,  $ AMT1 BEFORE DECIMAL   
                 35,02)  $ AMT1 AFTER DECIMAL     
                                                 
  OUTFIL REMOVECC,                               
  OUTREC=(01,80),                                 
  TRAILER1=(37C'-',/,                             
             'SUM  OF EACH ITEM: ',               
              TOT=(81,09,ZD,EDIT=(IIIIIIT.TT)),/,
              37C'-')                             
/*                                               


output will be as below.
Code:
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
-------------------------------------
SUM  OF EACH ITEM:   49380.88       
-------------------------------------


Last edited by vkphani on Wed Sep 19, 2007 4:16 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
vivek1983
Intermediate


Joined: 20 Apr 2006
Posts: 222
Topics: 24
PostPosted: Wed Sep 19, 2007 4:06 am    Post subject: Reply with quote
suresh_d,

Check the below link:

http://www.mvsforums.com/helpboards/viewtopic.php?t=8650&highlight=sum+fields+decimal
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
Back to top
View user's profile Send private message
suresh_d
Beginner


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Wed Sep 19, 2007 6:44 am    Post subject: Reply with quote
Hi vkphani,

Both of your codes worked fine.. But can you pls tell if I can get the output of count into a file rather than sysout..

And in sort why is the TOT having fields from 81 position. Because M using files for sortin and sortout where the lenth is 50. M getting error if I use the code for files as ther is no 81 position in either of my files.

thanks,
Suresh.
Back to top
View user's profile Send private message
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Wed Sep 19, 2007 8:30 am    Post subject: Reply with quote
suresh_d wrote:
Hi vkphani,

Both of your codes worked fine.. But can you pls tell if I can get the output of count into a file rather than sysout..

And in sort why is the TOT having fields from 81 position. Because M using files for sortin and sortout where the lenth is 50. M getting error if I use the code for files as ther is no 81 position in either of my files.

thanks,
Suresh.


Suresh,

If you are getting error if you use 81, then replace 80 by 50 and 81 by 51 as below. It works fine.
Code:
 //SYSIN    DD *                                 
   SORT FIELDS=COPY                               
   OUTREC FIELDS=(01,50,                         
                  27,07,  $ AMT1 BEFORE DECIMAL   
                  35,02)  $ AMT1 AFTER DECIMAL   
                                                 
   OUTFIL REMOVECC,                               
   OUTREC=(01,50),                               
   TRAILER1=(37C'-',/,                           
              'SUM  OF EACH ITEM: ',             
               TOT=(51,09,ZD,EDIT=(IIIIIIT.TT)),/,
               37C'-')                           
 /*                                               



If you want the count to be written to an output file, you can use the below code:

Code:
//CTRCDS EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSIN DD 
OPTION COPY
OUTFIL NODETAIL,REMOVECC,
TRAILER1=(COUNT=(M11,LENGTH=8))
/*
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Sep 19, 2007 9:00 am    Post subject: Reply with quote
suresh_d,

The following DFSORT 1 step JCL will give you the count of records as well as the total of column4

Code:

//STEP0100 EXEC PGM=SORT                   
//SYSOUT   DD SYSOUT=*                     
//SORTIN   DD *                             
234526.05.2007 0123457.11 0012345.22       
234526.05.2007 0123457.11 0012345.22       
234526.05.2007 0123457.11 0012345.22       
234526.05.2007 0123457.11 0012345.22       
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                   
 SORT FIELDS=COPY                                 
 OUTFIL REMOVECC,                                 
 TRAILER1=(37C'*',/,                               
           'TOTAL NO OF RECORDS :    ',           
           COUNT=(M10,LENGTH=10),/,               
           'SUM OF ALL RECORDS  : ',               
           TOT=(27,10,UFF,EDIT=(IIIIIIIIIT.TT)),/,
           37C'*')                                 
/*                                                 


The output is :

Code:

234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
234526.05.2007 0123457.11 0012345.22
*************************************
TOTAL NO OF RECORDS :           4     
SUM OF ALL RECORDS  :    49380.88     
*************************************


Hope this helps...

Cheers

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


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Wed Sep 19, 2007 9:59 am    Post subject: Reply with quote
Kolusu,

Thanks for the smaller sigle JCL. Its working fine.

vkphani,

thanks for your prompt replies. Those codes worked, but I preferred Kolusu's single JCL. Hope you don't mind Smile

Now, if you people dont get irritated.. can you please tell how to write the output of above jcl without input record's in the o/p file.

thanks,
Suresh.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Sep 19, 2007 10:05 am    Post subject: Reply with quote
Quote:

Now, if you people dont get irritated.. can you please tell how to write the output of above jcl without input record's in the o/p file.


suresh_d,

use the following control cards

Code:

//SYSIN    DD *                                   
 SORT FIELDS=COPY                                 
 OUTFIL NODETAIL,REMOVECC,                                 
 TRAILER1=(37C'*',/,                               
           'TOTAL NO OF RECORDS :    ',           
           COUNT=(M10,LENGTH=10),/,               
           'SUM OF ALL RECORDS  : ',               
           TOT=(27,10,UFF,EDIT=(IIIIIIIIIT.TT)),/,
           37C'*')                                 
/*           


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


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Wed Sep 19, 2007 10:23 am    Post subject: Reply with quote
Kolusu,

It worked fine...

thanks a ton

thanks,
Suresh.
Back to top
View user's profile Send private message
suresh_d
Beginner


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Thu Sep 20, 2007 5:58 am    Post subject: Reply with quote
Hi,

I need to format the O/P still. The total amount needs to have 9 decimal and 9 integer part. I jus chaged the edit part to EDIT=(IIIIIIIIT.TTTTTTTTT). But it is not working. Can you please help.

Thanks,
Suresh.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Sep 20, 2007 7:42 am    Post subject: Reply with quote
Quote:

need to format the O/P still. The total amount needs to have 9 decimal and 9 integer part. I jus chaged the edit part to EDIT=(IIIIIIIIT.TTTTTTTTT).


suresh_d,

You shouldn't get any error with that edit mask. When ever you say something does not work you need to post the complete sysout messages.

Try these control cards

Code:

//SYSIN    DD *                                         
 SORT FIELDS=COPY                                       
 OUTFIL NODETAIL,REMOVECC,                                       
 TRAILER1=(37C'*',/,                                     
           'TOTAL NO OF RECORDS :    ',                 
           COUNT=(M10,LENGTH=10),/,                     
           'SUM OF ALL RECORDS  : ',                     
           TOT=(27,10,UFF,EDIT=(IIIIIIIIIT.TTTTTTTTT)),/,
           37C'*')                                       
/*                                                       



Also you say you want 9 integer numbers and 9 decimal numbers which is a total 19 bytes (including the decimal dot) it does not make sense. Remember that you are summing only a 10 byte field and you want a 19 byte output field? If you need 9 decimals then the output sum be as follows

Code:

0.004938088


instead of
Code:

49380.88     


Do you really need it like that?

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


Joined: 20 Sep 2006
Posts: 33
Topics: 9
PostPosted: Thu Sep 20, 2007 9:23 am    Post subject: Reply with quote
Hi Kolusu,

Sorry for not being clear in my question. The above code didn't give error. It didn't give the desired o/p that's it.

Now I got the answer. I just made the i/p t0 19.

thanks,
Suresh.
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