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
 

 

Get Record count and total using sort

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

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Wed Nov 07, 2012 11:50 am    Post subject: Get Record count and total using sort
Reply with quote

Hi,

I have an input file as below.
Code:
AA
AA
AA
BB
BB


I need to get the count of each record occurence and total and map it to another file with the count and total.

second file as below ,there is flexiblity to change its data.

Code:
AA
BB
CC
TO

I should get an output like below,
Code:

AA,'     3'
BB,'     2'
CC,'000000'
TO,'     5' 


TO:is for total.

I have achieved this with the below.

Code:
//STEP    EXEC SORT                                       
//SYSOUT  DD SYSOUT=*                                     
//SORTIN  DD *                                             
AA                                                         
AA                                                         
AA                                                         
BB                                                         
BB                                                         
//SORTOUT DD DISP=(,PASS),DSN=&&TEMP1,UNIT=SYSDA           
//SYSIN DD *                                               
 OPTION COPY                                               
 OUTFIL REMOVECC,NODETAIL,BUILD=(80:X),                   
 SECTIONS=(1,2,TRAILER3=(1,2,C',',COUNT=(M10,LENGTH=6))), 
 TRAILER1=('TO',C',',COUNT=(M10,LENGTH=6))                 
//*********************************************************
//STEP     EXEC SORT                                       
//SYSOUT   DD SYSOUT=*                                     
//SORTJNF1 DD DISP=SHR,DSN=&&TEMP1                         
//SORTJNF2 DD *                                           
AA,'                                                       
BB,'                                                       
CC,'                                                       
TO,'                                                       
//SORTOUT DD SYSOUT=*                                     
//SYSIN DD *                                               
  JOINKEYS FILES=F1,FIELDS=(1,2,A)                         
  JOINKEYS FILES=F2,FIELDS=(1,2,A)                         
  REFORMAT FIELDS=(F2:1,4,F1:4,6)                         
  JOIN UNPAIRED F2,F1 ONLY                                 
  OPTION COPY                                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(5:5,6,C'''')),         
         IFTHEN=(WHEN=(5,6,CH,EQ,C' '),OVERLAY=(5:6C'0')) 



If anyone has a one step solution or a better idea,please let me know.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Wed Nov 07, 2012 1:25 pm    Post subject: Reply to: Get Record count and total using sort
Reply with quote

SUM in JNF1CNTL.

After the JOIN use OUTREC/OUTFIL to change the unmatched on 2 to contain the weird "zero" figure.

Use the OUTFIL to create the total record/trailer record.

Add the "formatting" of the fields (comma and quotes) as late as you can.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Wed Nov 07, 2012 3:54 pm    Post subject:
Reply with quote

thanks bill,
while i was trying your suggestion, I was not able to do the sum in joinkey subtask JNF1CNTL,was getting error as sort fields not supported in joinkey subtask,if you have any sample card for this pls let me know.

and in the meantime i got a simpler solution for my problem.

Code:
//STEP    EXEC SORT                                           
//SYSOUT  DD SYSOUT=*                                         
//SORTIN  DD *                                               
AA                                                           
AA                                                           
BB                                                           
//        DD *                                               
AA                                                           
BB                                                           
CC                                                           
//SORTOUT DD SYSOUT=*                                         
//SYSIN DD *                                                 
 OPTION ZDPRINT                                               
 INREC FIELDS=(1,2,X,C'000001')                               
 SORT FIELDS=(1,2,CH,A)                                       
 SUM FIELDS=(4,6,ZD)                                         
 OUTREC BUILD=(1,3,4:(4,6,ZD,SUB,+1),EDIT=(TTTTTT))           
 OUTFIL REMOVECC,BUILD=(1,2,C',''',4,6,C'''',80:X),           
 TRAILER1=('TO',C',''',TOT=(4,6,ZD,TO=ZD,LENGTH=6),C'''',80:X)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Wed Nov 07, 2012 4:17 pm    Post subject: Reply to: Get Record count and total using sort
Reply with quote

Can you post the full sysout for the error on JNF1CNTL, please?

With your new solution, don't you get "extra" records counted from the second file? And what about the format of the leading zeros which you wanted? And where has your "zero" record gone for C? (these are all inter-connected).
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Wed Nov 07, 2012 4:43 pm    Post subject:
Reply with quote

sysout:
Code:

ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL                                   
ICE417I 0 THIS IS THE JOINKEYS F1 SUBTASK FOR SORTJNF1                       
ICE000I 0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 11:59 ON WED
           OPTION ZDPRINT                                                     
           INREC FIELDS=(1,2,X,C'000001')                                     
           SORT FIELDS=(1,2,CH,A)                                             
ICE427A 0 SORT     STATEMENT CANNOT BE USED WITH JOINKEYS SUBTASK             
ICE751I 0 C5-K76982 C6-K90026 E7-K70685                                       
ICE146I 1 END OF STATEMENTS FROM JNF1CNTL - PARAMETER LIST STATEMENTS FOLLOW 
ICE052I 3 END OF DFSORT 



for extra record ,i am using the SUBstract,i would get the o/p as
Code:
AA,'000002'
BB,'000001'
CC,'000000'
TO,'000003'
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Wed Nov 07, 2012 4:54 pm    Post subject: Reply to: Get Record count and total using sort
Reply with quote

OK, you get the SORT automatically from just having the JOINKEYS. So you can use SUM.

Drop the SORT, include the SUM and check the sysout, you'll see the generated SORT statement.
Back to top
View user's profile Send private message
knickraj
Warnings : 1

New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro

PostPosted: Wed Nov 07, 2012 6:57 pm    Post subject: Reply to: Get Record count and total using sort
Reply with quote

thanks bill
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 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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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