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 for a group of records

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


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Wed Feb 17, 2010 5:07 pm    Post subject: Sum for a group of records Reply with quote
Hi All,

I have a input fileLRECL=80, RECFM=FB and data is as follows

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
00549517 567171       0.00     27.65     60.00
00549517 567171      44.40     69.29    158.00
00591411 572040       0.00    130.06    550.00
00591411 572040       0.00    130.06    550.00
00591411 572040       0.00    130.06    550.00


Key is 1 to 15 and I want add the amount field from 16 to 26,27 to 36 and 37 to 46

The output should be


Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+-
00549517 567171       0.00     27.65     60.00     44.40     96.94    218.00
00549517 567171      44.40     69.29    158.00     44.40     96.94    218.00
00591411 572040       0.00    130.06    550.00      0.00    390.18   1650.00
00591411 572040       0.00    130.06    550.00      0.00    390.18   1650.00
00591411 572040       0.00    130.06    550.00      0.00    390.18   1650.00


Can you please let me know how to do this by using SORT utility.

Thanks,
_________________
Regards,
Chandra
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 Feb 17, 2010 6:08 pm    Post subject: Reply with quote
Chandra,

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), DFSORT now supports the JOINKEYS function which can sum sort the file on the key and write it on all the detailed records. The trick here is use the same input file for both SORTJNF1 and SORTJNF2. Sum sort the second file and perform the match
Code:

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SORTJNF1 DD DSN=your input file,DISP=SHR
//SORTJNF2 DD DSN=your input file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                       
  JOINKEYS FILES=F1,FIELDS=(1,15,A)                       
  JOINKEYS FILES=F2,FIELDS=(1,15,A)                       
  REFORMAT FIELDS=(F1:1,46,F2:81,18)                     
  OUTREC BUILD=(1,46,47,6,PD,EDIT=(IIIIIIIT.TT),         
                     53,6,PD,EDIT=(IIIIIIT.TT),           
                     59,6,PD,EDIT=(IIIIIIT.TT),80:X)     
//JNF2CNTL DD *                                           
  INREC OVERLAY=(81:16,11,UFF,PD,LENGTH=6,               
                    27,10,UFF,PD,LENGTH=6,               
                    37,10,UFF,PD,LENGTH=6)               
  SUM FIELDS=(81,6,87,6,93,6),FORMAT=PD                   
//*


The output from the above job is
Code:

00549517 567171       0.00     27.65     60.00      44.40     96.94    218.00
00549517 567171      44.40     69.29    158.00      44.40     96.94    218.00
00591411 572040       0.00    130.06    550.00       0.00    390.18   1650.00
00591411 572040       0.00    130.06    550.00       0.00    390.18   1650.00
00591411 572040       0.00    130.06    550.00       0.00    390.18   1650.00

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


Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
PostPosted: Thu Feb 18, 2010 12:27 am    Post subject: Reply with quote
Hi Kolusu,

i tried the same jcl.. but its giving me an error.. may be the version was different.. but it shows V1R5

Code:
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                             
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 00:20 ON THU FEB 18, 2010 -
            SORT FIELDS=COPY                                                 
            JOINKEYS FILES=F1,FIELDS=(1,15,A)                               
            $                                                               
ICE005A 0 STATEMENT DEFINER ERROR                                           
            JOINKEYS FILES=F2,FIELDS=(1,15,A)                               
            $                                                               
ICE005A 0 STATEMENT DEFINER ERROR                                           
            REFORMAT FIELDS=(F1:1,46,F2:81,18)                               
            $                                                               
ICE005A 0 STATEMENT DEFINER ERROR                                           
            OUTREC BUILD=(1,46,47,6,PD,EDIT=(IIIIIIIT.TT),                   
                               53,6,PD,EDIT=(IIIIIIT.TT),                   
                               59,6,PD,EDIT=(IIIIIIT.TT),80:X)               
ICE056A 0 SORTIN   NOT DEFINED                                               
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E7-K24705                 
ICE052I 3 END OF DFSORT


May be a SORIN is needed?
_________________
Thanks
Back to top
View user's profile Send private message Yahoo Messenger
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36
PostPosted: Thu Feb 18, 2010 10:10 am    Post subject: Reply with quote
Hi Kolusu,

It is working fine.

Thank you !!
_________________
Regards,
Chandra
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 Feb 18, 2010 11:59 am    Post subject: Reply with quote
edkir98,

Your messages indicate that you don't have the UK51706 installed. To determine which level of DFSORT functions you have available, look at message ICE201I in the //SYSOUT messages you receive from the DFSORT job shown below, or from any successful DFSORT job.

Code:

//STEP0100 EXEC PGM=SORT       
//SYSOUT   DD SYSOUT=*         
//SORTIN   DD *                 
A                               
//SORTOUT  DD SYSOUT=*         
//SYSIN    DD *                 
  SORT FIELDS=COPY             
//*


If you see:

ICE201I G RECORD TYPE ...

the G indicates you have the Novemnber, 2009 DFSORT functions (JOINKEYS, TOJUL, TOGREG, WEEKDAY, etc) and all of the earlier functions. This function level corresponds to z/OS DFSORT V1R5 PTF UK51706 and z/OS DFSORT V1R10 PTF UK51707. You are completely up to date on DFSORT functional PTFs.

If you see:

ICE201I F RECORD TYPE ...

the F indicates you have the July, 2008 DFSORT functions (FINDREP, WHEN=GROUP, DATASORT, SUBSET, etc) and all of the earlier functions.
This function level corresponds to z/OS DFSORT V1R5 PTF UK90013 and z/OS DFSORT V1R10 PTF UK90014. You are behind on DFSORT functional PTFs. Ask your System Programmer to install z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707.

If you see:

ICE201I E RECORD TYPE ...

the E indicates you have the April, 2006 DFSORT functions (PARSE, JFY, SQZ, SPLIT1R, etc) and all of the earlier functions. This function level corresponds to z/OS DFSORT V1R5 PTF UK90007. You are behind on DFSORT functional PTFs. Ask your System Programmer to install z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707.

If you see:

ICE201I 0 RECORD TYPE ...

the 0 indicates you do not have the July, 2008 or April, 2006 DFSORT functions. You are way behind on DFSORT functional PTFs. Ask your System Programmer to install z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707.

You may or may not have the Dec, 2004 DFSORT functions (IFTHEN, OVERLAY, SFF, UFF, etc), which corresponds to z/OS DFSORT V1R5 PTF UQ95214. try using one of these functions - if you get errors trying to use it, then you don't even have the Dec, 2004 PTF installed.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
edkir98
Beginner


Joined: 27 Aug 2007
Posts: 102
Topics: 42
Location: Chennai
PostPosted: Thu Feb 18, 2010 12:36 pm    Post subject: Reply with quote
Hi Kolusu.. Thanks.. we have a ICE201I F
may be i need to wait some more time to work out this example Smile
_________________
Thanks
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Feb 18, 2010 12:53 pm    Post subject: Reply with quote
edkir98 wrote:
Hi Kolusu.. Thanks.. we have a ICE201I F
may be i need to wait some more time to work out this example Smile


edkir98,

You can get same results with the following.

Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
$$$                                                                   
//         DD *                                                       
00549517 567171       0.00     27.65     60.00                         
00549517 567171      44.40     69.29    158.00                         
00591411 572040       0.00    130.06    550.00                         
00591411 572040       0.00    130.06    550.00                         
00591411 572040       0.00    130.06    550.00                         
//         DD *                                                       
$$$                                                                   
//         DD *                                                       
00549517 567171       0.00     27.65     60.00                         
00549517 567171      44.40     69.29    158.00                         
00591411 572040       0.00    130.06    550.00                         
00591411 572040       0.00    130.06    550.00                         
00591411 572040       0.00    130.06    550.00                         
//SORTOUT  DD SYSOUT=*             
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(90:16,11,UFF,PD,LENGTH=6,           
                27,10,UFF,PD,LENGTH=6,37,10,UFF,PD,LENGTH=6)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(81:ID=1,SEQ=8)),   
  IFTHEN=(WHEN=(81,1,ZD,EQ,1),OVERLAY=(82:7C'0',C'1'))                 
                                                                       
  SORT FIELDS=(1,15,CH,A,82,8,CH,A)                                   
  SUM FIELDS=(90,6,96,6,102,6),FORMAT=PD                               
                                                                       
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,ZD,EQ,1),PUSH=(90:90,18)),     
  IFTHEN=(WHEN=(81,1,ZD,EQ,2),OVERLAY=(47:90,6,PD,EDIT=(IIIIIIIT.TT), 
          96,6,PD,EDIT=(IIIIIIT.TT),102,6,PD,EDIT=(IIIIIIT.TT)))       
                                                                       
  OUTFIL INCLUDE=(81,1,ZD,EQ,2),BUILD=(1,80)                           
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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