Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort Sum the records in a Data set

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Dec 03, 2010 2:28 am    Post subject: Sort Sum the records in a Data set
Reply with quote

Say input file is :

ADMIN DATE
123 20100501
123 20100501
123 20100501
123 20100501
124 20100501
124 20100501
124 20100501
127 20100502
127 20100502
127 20100502



I have to sort on admin and date and do a sort sum such that
the expected output file must be :

123 on 20100501 = 4
124 on 20100501 = 3
127 on 20100502 = 3
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Dec 03, 2010 2:39 am    Post subject: Reply to: Sort Sum the records in a Data set
Reply with quote

techslam,

Assuming that the data is already sorted on admin and date field , the following DFSORT JCL will give you the desired results. If the data is not sorted change SORT FIELDS=COPY to SORT FIELDS=(1,12,CH,A)
Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT    DD SYSOUT=*                                               
//SORTIN    DD *                                                     
123 20100501                                                         
123 20100501                                                         
123 20100501                                                         
123 20100501                                                         
124 20100501                                                         
124 20100501                                                         
124 20100501                                                         
127 20100502                                                         
127 20100502                                                         
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
127 20100502                                                         
//SORTOUT   DD SYSOUT=*                                               
//SYSIN     DD *                                                     
  SORT FIELDS=COPY                                                   
  OUTFIL REMOVECC,NODETAIL,                                           
  SECTIONS=(1,12,TRAILER3=(1,3,' ON ',5,8,' = ',COUNT=(M10,LENGTH=3)))
//*


The output of this job is
Code:

123 ON 20100501 =   4
124 ON 20100501 =   3
127 ON 20100502 =   3
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Dec 03, 2010 4:45 am    Post subject:
Reply with quote

Thanks Skolusu,

Actually i have an input file whose LRECL = 4508.

Admin starts from 1 and length is 9
Date starts from 4501 and length is 8

So in this case how do I code the sort card.

I tried following, but I am getting abend :

Code:

//SYSIN     DD *                                                     
  SORT FIELDS=(1,9,4501,4508,CH,A)                                   
  OUTFIL REMOVECC,NODETAIL,                                           
  SECTIONS=(1,4508,                                                   
           TRAILER3=(1,9,' ON ',4501,4508,' = ',COUNT=(M10,LENGTH=9)))
//*                                                                   


Kindly advice.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Dec 03, 2010 5:45 am    Post subject: Reply to: Sort Sum the records in a Data set
Reply with quote

techslam,

Use the following control cards which would create a 30 byte output file
Code:

//SYSIN     DD *                                         
  INREC BUILD=(1,9,4501,8)                               
  SORT FIELDS=(1,17,CH,A)                               
                                                         
  OUTFIL REMOVECC,NODETAIL,BUILD=(30X),                 
  SECTIONS=(1,17,                                       
  TRAILER3=(1,9,' ON ',10,8,' = ',COUNT=(M10,LENGTH=6)))
//*
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Dec 03, 2010 8:07 pm    Post subject:
Reply with quote

Thanks a lot Skolusu,

I have modified it according to my requirement and it worked fine.
Code:

//SYSIN     DD *                                       
  INREC BUILD=(1,9,4414,1,4443,1,4501,8)               
  SORT FIELDS=(1,9,CH,A,10,1,CH,A,11,1,CH,A,12,8,CH,A) 
                                                       
  OUTFIL REMOVECC,NODETAIL,BUILD=(30X),                 
  SECTIONS=(1,19,                                       
  TRAILER3=(1,9,' ON ',12,8,' = ',COUNT=(M10,LENGTH=6)))
//*                                                     
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Dec 03, 2010 8:12 pm    Post subject:
Reply with quote

I can see, you have moved the required fields to a different file and then sorted it from there and performed the count from there. What will be my code, if I want to view that sorted file with required fields?
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Dec 03, 2010 10:30 pm    Post subject:
Reply with quote

Hi

I have used the latest sort control statements given here by Skolusu.
It works fine for a small test file will less number of records,
But when I gave the original input file which has 1250000 records in it, there is issue in the out put file generated :

Code:

123456789 ON 20100701 =      2
123456789 ON 20100707 =   2006
123456789 ON 20100702 =     14
123456789 ON 20100706 =      2
123456789 ON 20100701 =      4


here for the date 20100701 there are two counts, 2 and 4, it should have been
Code:

123456789 ON 20100701 =      6


What is causing this issue to occur ?

It happens with many admin id's in the file.
Please advice....
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Dec 04, 2010 12:21 am    Post subject:
Reply with quote

techslam wrote:
Hi

I have used the latest sort control statements given here by Skolusu.
It works fine for a small test file will less number of records,
But when I gave the original input file which has 1250000 records in it, there is issue in the out put file generated
:


NO. You did not use the same control cards as I have shown as you added 2 more fields from pos 4414 for 1 byte and from position 4443 for 1 byte. These 2 bytes are also used in the control break processing and hence the wrong results. I used 17 bytes for key break where as you used 19 bytes for the key break. Please don't attribute your mistakes as mine.

techslam wrote:
I can see, you have moved the required fields to a different file and then sorted it from there and performed the count from there. What will be my code, if I want to view that sorted file with required fields?


I can't keep working on the same requirement over and over whenever you change the requirements. You need to do a better job of explaining the complete requirement in one go rather than in bits and pieces.
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Sat Dec 04, 2010 3:26 am    Post subject:
Reply with quote

Hi Skolusu,

I appreciate you helping me out in this requirement and I apologise for making changes in my requirements. I have worked on your code and now I am getting the desired output the way I wanted . It is given below

Code:

123456789 XP 20100701         2
123456789 XP 20100707      2006
123456789 XP 20100702        14
123456789 XP 20100706         2
123456789 YP 20100701         4


Everything looks fine here, I just need one final thing, I have made the counter of length 9 bytes and I want the counter output appended with zeros rather than spaces.

that is :

Code:

123456789 XP 20100701 000000002
123456789 XP 20100707 000002006
123456789 XP 20100702 000000014
123456789 XP 20100706 000000002
123456789 YP 20100701 000000004


Kindly advice. I really appreciate this help through this forums.

Thanks a lot.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Dec 04, 2010 3:36 am    Post subject:
Reply with quote

For leading zeros, use M11 rather than M10. See:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA50/TBLOFMASKX?SHELF=&DT=20100630155256&CASE=&ScrollTOP=TBLOFMASKX#TBLOFMASKX
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Sat Dec 04, 2010 3:48 am    Post subject:
Reply with quote

Thanks a lot Skolusu and Frank ,

I got my desired output.
Iam a newbie to DFSORT, so I really appreciate all the help provided here.
Getting Started DFSORT and DFSORT Application programming guide are awesome documents and I am reading them to get good knowledge.

Thanks a ton.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to move a long alphanumeric data ... lind sh COBOL Programming 3 Mon Dec 05, 2016 7:51 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us