# Percentage Calculation in SYNCSORT

Author Message
mistah kurtz

Active User

Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

Posted: Wed Nov 20, 2013 7:47 pm

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.
Pandora-Box

Global Moderator

Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

 Posted: Wed Nov 20, 2013 7:53 pm 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?
mistah kurtz

Active User

Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

Posted: Wed Nov 20, 2013 8:03 pm

 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%.
mistah kurtz

Active User

Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

Posted: Thu Nov 21, 2013 12:50 pm

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.
Bill Woodger

Moderator Emeritus

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

 Posted: Thu Nov 21, 2013 12:57 pm 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.
hailashwin

New User

Joined: 16 Oct 2008
Posts: 74
Location: Boston

Posted: Fri Nov 22, 2013 4:38 pm

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.
Pandora-Box

Global Moderator

Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

Posted: Fri Nov 22, 2013 5:36 pm

 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
Bill Woodger

Moderator Emeritus

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

Posted: Fri Nov 22, 2013 7:10 pm

 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.
Bill Woodger

Moderator Emeritus

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

 Posted: Fri Nov 22, 2013 7:46 pm 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.
mistah kurtz

Active User

Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

 Posted: Sat Nov 23, 2013 12:25 pm 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!!
Bill Woodger

Moderator Emeritus

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

 Posted: Sat Nov 23, 2013 3:38 pm 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.
mistah kurtz

Active User

Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel

Posted: Mon Nov 25, 2013 9:01 am

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.
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Null values are considered in Total c... DFSORT/ICETOOL 6 Count Records with a crietaria in a f... DFSORT/ICETOOL 5 DFSORT/SYNCSORT/ICETOOL JCL & VSAM 8 Allocated space calculation from DCOL... PL/I & Assembler 3 Syncsort "Y2C" Function SYNCSORT 1
Search our Forums:

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