|
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
|
|
|
|
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 |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| 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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
| 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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
hailashwin
New User
Joined: 16 Oct 2008
Posts: 74
Location: Boston
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
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 |
|
|
|
|
|
