IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Consolidating and summing file contents


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 09, 2016 10:50 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jun 10, 2016 12:51 am
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Jun 10, 2016 3:00 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jun 10, 2016 5:43 am
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Jun 10, 2016 10:39 am
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

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Aug 11, 2016 2:55 am
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: 88
Location: India

PostPosted: Thu Aug 11, 2016 5:10 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Aug 11, 2016 5:17 pm
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

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Aug 11, 2016 5:47 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Aug 11, 2016 5:59 pm
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

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Aug 11, 2016 6:05 pm
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: 88
Location: India

PostPosted: Thu Aug 11, 2016 6:14 pm
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

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Aug 11, 2016 6:26 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Aug 11, 2016 8:53 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
Search our Forums:

Back to Top