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
 

 

Consolidating and summing file contents

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
Kevin Lindsley

New User


Joined: 02 Jun 2016
Posts: 5
Location: us

PostPosted: Thu Jun 09, 2016 5:24 am    Post subject: Consolidating and summing file contents
Reply with quote

My input file has the following format with multiple records starting with 0, 1 and 9. Input data below:

Code:

0       20160607
0       20160607
0       20160607
1xxxxxxx1
1xxxxxxx2
1xxxxxxx3
1xxxxxxx1
1xxxxxxx1
1xxxxxxx2
9       000000003
9       000000001
9       000000002


My desired output file is to have only one of the first three records that start with 0, all of the records that start with 1 and only one of the last three records that start with 9, however that one last record needs to have the sum of the values that are represented in those records. Output data below:

Code:

0       20160607
1xxxxxxx1
1xxxxxxx2
1xxxxxxx3
1xxxxxxx1
1xxxxxxx1
1xxxxxxx2
9       000000006


How would I accomplish this with SYNCSORT? Thanks.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Thu Jun 09, 2016 10:50 am    Post subject: Reply to: Consolidating and summing file contents
Reply with quote

From what you've shown, it would be possible to INCLUDE= for the "1"s, and use OUTFIL reporting featers, HEADER1,REMOVECC and create the header and trailers. There is a COUNT field available for TRAILERn, you can even modify it if you don't want to count header or trailer or both.

The date on your header depends what you want. One way would be a JPn symbol to take a PARM from the JCL if your SyncSORT supports it.

Another is to make a symbol file (dictionary SyncSORT calls them) from one step (OPTION STOPAFT=1) and use that in the second step. Other ways are hard-coding and more, depending on your actual requirement.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Jun 10, 2016 12:51 am    Post subject:
Reply with quote

Isn't INCLUDE for '1's will lose all '9's for the TOT? also TS may need sum on the values associated with '9's instead of counting all '1's. please correct me.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Fri Jun 10, 2016 3:00 am    Post subject: Reply to: Consolidating and summing file contents
Reply with quote

The header and trailer on the file can be ignored if they can be recreated. The trailer is easy. A 9, some blanks, and a TOT/TOTAL, adjusted if necessary. TRAILER1 can do that.

HEADER1 can do a header, it does need a source for the date-like bit of data.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Jun 10, 2016 5:43 am    Post subject:
Reply with quote

Understood, But wouldn't INCLUDE take precedence over trailer1 at the time it does TOT and have no '9's left because of INCLUDE?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Fri Jun 10, 2016 10:39 am    Post subject: Reply to: Consolidating and summing file contents
Reply with quote

Yes. That is the point of the INCLUDE. The original 9-type records are redundant. Only one of the three is required, and the data required for one is constant (according to the sample) plus a count of the records.

Count of the records in an OUTFIL group is what TOT/TOTAL does. So the entire trailer record can be generated by TRAILERn.

The header is constant plus-some-data. The only issuer for HEADERn is the source of plus-some-data.

Perhaps I should read the question again when I have time :-)
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Thu Aug 11, 2016 2:55 am    Post subject:
Reply with quote

Three differently handled group of records do require three separate output files, within the same SYNCSORT step.

Code:

//SORT01   EXEC PGM=SYNCSORT               
//*                                         
//SYSOUT   DD  SYSOUT=*                     
//*                                         
//HEADER   DD  SYSOUT=*                     
//DETAIL   DD  SYSOUT=*                     
//TRAILER  DD  SYSOUT=*                     
//*                                         
//SYSIN    DD  *                           
 SORT FIELDS=(1,1,CH,A),                   
      EQUALS                               
 OUTFIL FNAMES=HEADER,                     
        INCLUDE=(1,1,CH,EQ,C'0'),           
        BUILD=(1,20),                       
        NODETAIL,REMOVECC,                 
        HEADER1=(1,20)                     
 OUTFIL FNAMES=DETAIL,                     
        INCLUDE=(1,1,CH,EQ,C'1'),           
        BUILD=(1,20)                         
 OUTFIL FNAMES=TRAILER,                       
        INCLUDE=(1,1,CH,EQ,C'9'),             
        BUILD=(1,20),                         
        NODETAIL,REMOVECC,                   
        TRAILER1=(1,8,                       
                  TOT=(9,9,ZD,M11,LENGTH=9)) 
 END                                         
//*-+----1----+----2----+----3----+----4----+-
//SORTIN   DD  *                             
0       20160607                             
0       20160607                             
0       20160607                             
1xxxxxxx1                                     
1xxxxxxx2                                     
1xxxxxxx3                                     
1xxxxxxx1                                     
1xxxxxxx1                                     
1xxxxxxx2                                     
9       000000003                             
9       000000001
9       000000002
//*               


The result looks as follows
Code:

---SORT01  .HEADER
0       20160607 
---SORT01  .DETAIL
1xxxxxxx1     
1xxxxxxx2     
1xxxxxxx3     
1xxxxxxx1     
1xxxxxxx1     
1xxxxxxx2     
---SORT01  .TRAILER
9       000000006 
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 65
Location: India

PostPosted: Thu Aug 11, 2016 5:10 pm    Post subject:
Reply with quote

Hello Kevin,

A simple method can be (assuming LRECL=80); tested on DFSORT, should work with SyncSORT too:

Code:

 OMIT COND=(1,1,CH,EQ,C'9')                           
 INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,1),             
                              PUSH=(81:ID=1)),       
       IFTHEN=(WHEN=(1,1,CH,EQ,C'1'),                 
                    BUILD=(1,81,82:SEQNUM,2,ZD))     
 SORT FIELDS=(81,3,CH,A)                             
 SUM FIELDS=NONE                                     
 OUTFILE REMOVECC,                                   
         BUILD=(1,80),                               
         TRAILER1=(C'9       ',COUNT-1=(M11,LENGTH=9))


Giving:

Code:

0       20160607 
1XXXXXXX1         
1XXXXXXX2         
1XXXXXXX3         
1XXXXXXX1         
1XXXXXXX1         
1XXXXXXX2         
9       000000006

Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Aug 11, 2016 5:17 pm    Post subject: Reply to: Consolidating and summing file contents
Reply with quote

Quote:
Three differently handled group of records do require three separate output files, within the same SYNCSORT step.


the TS ( Topic Starter ) requirement was

Quote:
My desired output file (*) is to have only one of the first three records that start with 0, all of the records that start with 1 and only one of the last three records that start with 9 ... ... ...


(*) one file
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Thu Aug 11, 2016 5:47 pm    Post subject: Re: Reply to: Consolidating and summing file contents
Reply with quote

enrico-sorichetti wrote:
the TS ( Topic Starter ) requirement was

Quote:
My desired output file (*) is to have only one of the first three records that start with 0, all of the records that start with 1 and only one of the last three records that start with 9 ... ... ...


(*) one file


Code:

//COMBINE EXEC PGM=IEBGENER
//SYSPRINT DD DUMMY
//SYSIN    DD DUMMY
//SYSUT1   DD  DSN=&&HEADER,...
//         DD  DSN=&&DETAILS,...
//         DD  DSN=&&TRAILER,...
//SYSUT2   DD  DISP=(NEW,CATLG),DSN=new.single.output.file,...
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Aug 11, 2016 5:59 pm    Post subject: Reply to: Consolidating and summing file contents
Reply with quote

instead of adding one consolidating step

why not check if the DFSORT approach works with SYSNCORT
and post the result ?
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Thu Aug 11, 2016 6:05 pm    Post subject:
Reply with quote

Abid Hasan wrote:
Hello Kevin,

A simple method can be (assuming LRECL=80); tested on DFSORT, should work with SyncSORT too:
Quote:

OMIT COND=(1,1,CH,EQ,C'9')
. . . . . . . . .
OUTFILE REMOVECC,
BUILD=(1,80),
TRAILER1=(C'9 ',COUNT-1=(M11,LENGTH=9))


Two details.

1) The requirements was: "last record needs to have the sum of the values that are represented in those records".

Parameter TRAILER calculates the total number of type-1 records (that is 6 in this example), while it needs to summarize all the field values from type-9 records (that is 000000003+000000001+000000002=000000006).

2) Statement OMIT just eliminates all type-9 records on input; so there is nothing to summarize from type-9 as required.
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 65
Location: India

PostPosted: Thu Aug 11, 2016 6:14 pm    Post subject:
Reply with quote

Hello Sergeyken,

Indeed, you're spot-on.
Yet, going by the data shared, it seemed that the 'number' present in the trailer was the count of records minus the header present. If you notice, there are 3 'xxx1' records, 2 'xxx2' records and 1 'xxx3' record. TS has not specified the significance of the number in trailer, since it matched my assumption, hence the usage of COUNT operator; though, it CAN be further trimmed with a bit more logic; kindly refer Mr. Woodger's earlier post which enunciates this.
Back to top
View user's profile Send private message
sergeyken

New User


Joined: 29 Apr 2008
Posts: 37
Location: Maryland

PostPosted: Thu Aug 11, 2016 6:26 pm    Post subject: Re: Reply to: Consolidating and summing file contents
Reply with quote

Bill Woodger wrote:
Count of the records in an OUTFIL group is what TOT/TOTAL does. So the entire trailer record can be generated by TRAILERn.


Some clarification

TOTAL/TOT
Use the TOTAL subparameter to specify that numeric data are to
be accumulated and totaled
at the end of a report, logical page, or
section.

COUNT
Use the COUNT subparameter to obtain a count of the number of
records
in either the entire report or a specific part of the report.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Aug 11, 2016 8:53 pm    Post subject:
Reply with quote

I think,all that matter is # of passes and the least in either case is better otherwise both the solution should work, Unless TS is really serious and wants to confirm how trailer is to be determined COUNT/TOT choice would remain open point.
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 -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 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 CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


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