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

Percentage Calculation in SYNCSORT
IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Wed Nov 20, 2013 7:47 pm
 Reply with quote
Hi,

I have an input file which looks like below:
Code:
ACTIVE    1A   1000
ACTIVE    3A   1000
INACTIVE  1B   1000
INACTIVE  3B   1000


Now I have to add two more columns in the above file showing the Percentage as status level and of total, i.e., my output file should look like:
Code:
STATUS   CODE   COUNT   % SUB   % TOTAL
ACTIVE    1A    1000    50.00%  25.00%
ACTIVE    3A    1000    50.00%  25.00%
SUB TOTAL       2000       
INACTIVE  1B    1000    50.00%  25.00%
INACTIVE  3B    1000    50.00%  25.00%
SUB TOTAL       2000       
GRAND TOTAL     4000


I'm thinking of writing a program, but before that I'm posting here to get any ideas/suggestions if I can do it using SORT. I'm using SYNCSORT FOR Z/OS 1.4.1.0R

Thanks.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Wed Nov 20, 2013 7:53 pm
 Reply with quote
How are you calculating percentage (Formula) and did you check the manual if SYNCSORT FOR Z/OS 1.4.1.0R support Division and multiplication?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Wed Nov 20, 2013 8:03 pm
 Reply with quote
Quote:
How are you calculating percentage (Formula)

% SUB Calculation:
There are two types of ACTIVE Status Code. Their sum is 2000. So for each Code type It will be (1000/2000)*100 = 50 %. Same with inactive.

% TOTAL Calculation:
There are total four recordds. Their total sum is 4000. So percentage for each status code is (1000/4000)*100 = 25%.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Thu Nov 21, 2013 12:50 pm
 Reply with quote
Hi,

I have tried few things and posting the results here.
Code:
//STEP01   EXEC PGM=SORT                                           
//SORTIN   DD *                                                     
ACTIVE    1A   1000                                                 
ACTIVE    3A   1000                                                 
INACTIVE  1B   1000                                                 
INACTIVE  3B   1000                                                 
//SORTOUT  DD SYSOUT=*                                             
//SYSOUT   DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION  COPY                                                     
  OUTFIL   REMOVECC,                                               
           HEADER1=(01:C'STATUS',11:C'CODE',16:C'COUNT',25:C'% SUB',
                    31:C'%TOTAL'),                                 
           SECTIONS=(01,10,                                         
           HEADER3=(/,1:1,10),                                       
           TRAILER3=(/,1:C'SUBTOTAL :',                             
                       16:TOT=(16,4,ZD,EDIT=(TTTT)),/)),           
           TRAILER1=(1:'GRAND TOTAL: ',                             
                       16:TOT=(16,4,ZD,EDIT=(TTTT))),               
           BUILD=(10X,11:11,70)                                     


Output:
Code:
STATUS    CODE COUNT    % SUB %TOTAL
                                   
ACTIVE                             
          1A   1000                 
          3A   1000                 
                                   
SUBTOTAL :     2000                 
                                   
                                   
INACTIVE                           
          1B   1000                 
          3B   1000                 
                                   
SUBTOTAL :     2000                 
                                   
GRAND TOTAL:   4000                 

But now I'm not getting how to calculate the % using the Sub Total and Total. Can someone provide some pointers on how to proceed further.

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 Nov 21, 2013 12:57 pm
 Reply with quote
By the time you print the first line, you already need to know the Total and Subtotal.

Is the number of lines fixed, or limited in some other way?

I don't think your SyncTool has RESIZE, which would have allowed you to easily get all the records into one record, leaving you just the task of formatting the output from one record.

Without RESIZE, you have to do the work of RESIZE yourself. Using GROUPs on a field set with SEQNUM, PUSHing all the data you need, for a decreasing number of RECORDS=.

INCLUDE= the final SEQNUM only into OUTFIL. Calculate and format.
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 74
Location: Boston
PostPosted: Fri Nov 22, 2013 4:38 pm
 Reply with quote
Hi,
I don't think you would be able to achieve this in a single step.

I tried with a two step process after the step you had already posted.
Assuming it to be a 80 byte data file, it should work this way..

Code:

//STEP010  EXEC PGM=SORT                 
//SYSUDUMP DD  SYSOUT=*                 
//SYSOUT   DD  SYSOUT=*                 
//SORTIN   DD  *                         
STATUS    CODE COUNT    % SUB %TOTAL     
                                         
ACTIVE                                   
          1A   1000                     
          3A   2000                     
                                         
SUBTOTAL :     3000                     
                                         
                                         
INACTIVE                                 
          1B   1000                     
          3B   1000                     
                                         
SUBTOTAL :     2000                     
                       
GRAND TOTAL:   4000   
//SORTOUT  DD  DSN=XXXX.TEMP1,                       
//             DISP=(NEW,CATLG,DELETE),                         
//             UNIT=(SYSDA),SPACE=(CYL,(1,1),RLSE),             
//             DCB=(MODLDSCB,RECFM=FB,BLKSIZE=0,BUFNO=25)       
//*                                                             
//SYSIN    DD  *                                               
  SORT FIELDS=(81,5,CH,D)                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,5,ZD))             
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,12,CH,EQ,C'GRAND TOTAL:'),
        PUSH=(90:16,4)),                                       
         IFTHEN=(WHEN=GROUP,BEGIN=(1,10,CH,EQ,C'SUBTOTAL :'),   
        PUSH=(100:16,4))                                       
//*---------------------------------------------------------------------
//STEP020  EXEC PGM=SYNCSORT                                           
//SYSUDUMP DD  SYSOUT=*                                                 
//SYSOUT   DD  SYSOUT=*                                                 
//SORTIN   DD  DSN=XXXX.TEMP1,DISP=SHR                       
//SORTOUT  DD  DSN=XXXX.TEMP2,                               
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=(SYSDA),SPACE=(CYL,(1,1),RLSE),                     
//             DCB=(MODLDSCB,RECFM=FB,BLKSIZE=0,BUFNO=25)               
//*                                                                     
//SYSIN    DD  *                                                       
  SORT FIELDS=(81,5,CH,A)                                               
    INREC IFTHEN=(WHEN=(16,4,FS,EQ,NUM),                               
      OVERLAY=(32:16,4,ZD,MUL,+10000,DIV,90,4,ZD,EDIT=(TT.TT),37:C'%'))
    OUTREC IFTHEN=(WHEN=(100,4,FS,EQ,NUM,AND,16,4,FS,EQ,NUM),           
     OVERLAY=(25:16,4,ZD,MUL,+10000,DIV,100,4,ZD,EDIT=(TT.TT),30:C'%'))
    OUTFIL IFTHEN=(WHEN=INIT,BUILD=(1,80)),                             
           IFTHEN=(WHEN=(1,10,CH,EQ,C'SUBTOTAL :',OR,                   
                         1,12,CH,EQ,C'GRAND TOTAL:'),                   
           OVERLAY=(25:C'             '))                               
//*                                                                     



Output:

Code:

STATUS    CODE COUNT    % SUB %TOTAL                   
                                                       
ACTIVE                                                 
          1A   1000     33.33% 25.00%                   
          3A   2000     66.66% 50.00%                   
                                                       
SUBTOTAL :     3000                                     
                                                       
                                                       
INACTIVE                                               
          1B   1000     50.00% 25.00%                   
          3B   1000     50.00% 25.00%                   
                                                       
SUBTOTAL :     2000                                     
                                                       
GRAND TOTAL:   4000                                     


Thanks,
Ashwin.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Fri Nov 22, 2013 5:36 pm
 Reply with quote
Code:
//SORTIN   DD *
ACTIVE    1A   1000
ACTIVE    3A   1000
INACTIVE  1B   1000
INACTIVE  3B   1000
//SORTOUT  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,EQ,C'ACTIVE'),
        END=(1,8,CH,EQ,C'ACTIVE'),PUSH=(30:16,4)),
        IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,EQ,C'INACTIVE'),
        PUSH=(40:16,4)),
        IFTHEN=(WHEN=(30,4,CH,EQ,C'    '),OVERLAY=(1:15X,30:C'0000')),
        IFTHEN=(WHEN=(40,4,CH,EQ,C'    '),OVERLAY=(1:15X,40:C'0000'))
  SORT FIELDS=(1,8,CH,A)
  SUM FIELDS=(16,4,ZD,30,4,ZD,40,4,ZD)
  OUTFIL BUILD=(1:C'GTO,',5:16,4,/,
          1:C'SBA,',5:30,4,/,
          1:C'SBI,',5:40,4)


Output

Code:
GTO,4000
SBA,2000
SBI,2000


With Symnames you could get it done in two steps including the one above
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 Nov 22, 2013 7:10 pm
 Reply with quote
Code:
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,1,ZD)),
       IFTHEN=(WHEN=GROUP,
                 BEGIN=(81,1,CH,EQ,C'1'),
                 RECORDS=4),
                 PUSH=(82:1,80)),
       IFTHEN=(WHEN=GROUP,
                 BEGIN=(81,1,CH,EQ,C'2'),
                 RECORDS=3),
                 PUSH=(162:1,80)),
       IFTHEN=(WHEN=GROUP,
                 BEGIN=(81,1,CH,EQ,C'3'),
                 RECORDS=2),
                 PUSH=(242:1,80))


  OUTFIL INCLUDE=(81,1,CH,EQ,C'4')

The output., subject to typos, should be one long record containing all the input records. The first 80 bytes are the last record, then one byte count (set to '4') then 80 bytes from the first record, then 80 bytes from the second record, then 80 bytes from the third record.

If you take out the OUTFIL, you will see four long records, and you can get the idea of how the data is built up, The RECORDS= are for neatness and reuse. If there are only four input records, they are not needed. The SEQNUM is used so that the data from each individual record will end up in its own position on the long record.

Then just mess with the data and use BUILD with /, the slash-operator, to produce the required format output on multiple records/lines.

Obviously the whole 80 bytes are not needed, but I've no time to count columns or test.
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 Nov 22, 2013 7:46 pm
 Reply with quote
Yes, when there is only one value for a data-item, or when the multiples can be dealt with easily (there are two, and only two, sub-totals) then symbols/SYMNAMES can be used by pre-processing the file and obtaining the values.

In the case of repetitions of the grouped data, symbols/SYMNAME will not work conveniently, or not work at all.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Sat Nov 23, 2013 12:25 pm
 Reply with quote
Thanks Ashwin, Pandora box and Bill.

Number of lines is not fixed and can have duplicates. I had posted here some represenational data, related to the part of the report where I was facing problems.

Thank you all for posting yur ideas here. I have found a working solution, using the methods that have been suggested here.

Thanks again!!
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: Sat Nov 23, 2013 3:38 pm
 Reply with quote
Would you like to show us what you got to?

Please, when asking, show a representative sample of the data. If you show one group of four, it looks like that is what you have.

You also know to answer questions that are asked if you want to make progress with the answers.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Mon Nov 25, 2013 9:01 am
 Reply with quote
Sorry I have not been very clear. I just needed an idea. So I chose to remove certain details which I felt I can handle. I just wanted to keep my post simple and to the point.

The whole report consists of several sections. One of the section was to calculate the percentage calculation.

So I have created seperate SORT steps to produce each of the section. one or two steps for each section.

For this particular section, There could have been duplicates in the Input file, which I found later, after I had posted here.

i.e My input file may have records like below:
Code:
ACTIVE    1A   1000
ACTIVE    1A   500
ACTIVE    3A   1000
INACTIVE  1B   1000
INACTIVE  3B   1000


So my first step is to remove the duplicates and calculate the Sub total and Grand total.

Now in next steps were to calculate the Percentage and get the alignement. I'm still halfway..I will post my final solution for this particular section.
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 Syncsort help SYNCSORT 3
No new posts SYNCSORT-DB2 interface SYNCSORT 0
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us