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

DFSORT - percentages
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 9:00 pm
 Reply with quote
Is it possible to generate a percentage on a section break (trailer3) ?


Input columns:
department, actual amount, budget amount

sampe file:
010,0000010,0000012
020,0000020,0000021
030,0000030,0000035

Desired output:

Code:
department  actual amount  budgent amount variance  percent
010              10              12           2      16.67
020              20              21           1       4.76
030              30              35           5      12.29

totals                           68           8      11.76
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
PostPosted: Mon May 04, 2009 9:10 pm
 Reply with quote
What formula are you using for the percentage?
Have you looked at TRAILER3?
Back to top
View user's profile Send private message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 9:14 pm
 Reply with quote
Yes, i have looked at trailer3.
The formula is (budget amount - actual amount) * 100 / variance.
variance = budget amount - actual amount.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Mon May 04, 2009 9:36 pm
 Reply with quote
You can do TOT, MIN, MAX and AVG in TRAILER3, but you can't do arithmetic. You can do arithmetic in BUILD (ADD, SUB, MUL, DIV, MOD). How about doing arithmetic in BUILD and adding extra fields with the results at the end of each record so you can use TOT in TRAILER3 for them?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon May 04, 2009 9:39 pm
 Reply with quote
jr3011 wrote:
Yes, i have looked at trailer3.
The formula is (budget amount - actual amount) * 100 / variance.
variance = budget amount - actual amount.


Jr3011,

You got the formula wrong based on the output you have shown. I think the right formula is
Code:

percentage = ((budget amount - actual amount) / budget amount) * 100
Back to top
View user's profile Send private message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 9:40 pm
 Reply with quote
totaling on a percentage will not give the correct results.
Back to top
View user's profile Send private message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 9:45 pm
 Reply with quote
I think either formual yeild the same results.
Back to top
View user's profile Send private message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 10:32 pm
 Reply with quote
I guess i stumped you guys ?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon May 04, 2009 10:34 pm
 Reply with quote
jr3011,

The following JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                               
010,0000010,0000012                                           
020,0000020,0000021                                           
030,0000030,0000035                                           
//OUT1     DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)     
//OUT2     DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)     
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                             
  INREC OVERLAY=(20:C',',13,7,ZD,SUB,5,7,ZD,M11,LENGTH=7,C',',
                 (+10000,MUL,21,7,ZD),DIV,13,7,ZD,EDIT=(TT.TT))
                                                               
  OUTFIL FNAMES=OUT1,REMOVECC,                                 
  BUILD=(1:1,3,13:5,7,ZD,29:13,7,ZD,43:21,7,ZD,54:29,5,80:X), 
  HEADER1=('DEPARTMENT  ACTUAL AMOUNT  BUDGET',               
           ' AMOUNT VARIANCE  PERCENT',80:X),                 
  TRAILER1=(80X)                                               
  OUTFIL FNAMES=OUT2,REMOVECC,NODETAIL,                       
  TRAILER1=('TOTALS',29:TOT=(13,7,ZD,M10,LENGTH=7),           
                     43:TOT=(21,7,ZD,M10,LENGTH=7),80:X)       
/*                                                             
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=&&T1,DISP=SHR                               
//         DD DSN=&&T2,DISP=SHR                               
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                             
  INREC IFTHEN=(WHEN=(1,6,CH,EQ,C'TOTALS'),                   
  OVERLAY=(54:(+10000,MUL,43,7,FS),DIV,29,7,FS,EDIT=(TT.TT))) 
/*                                                             
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon May 04, 2009 10:35 pm
 Reply with quote
jr3011 wrote:
I guess i stumped you guys ?


Jeez you couldn't wait for 45 mins ? wow We do have a regular job to attend
Back to top
View user's profile Send private message
jr3011

New User


Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL
PostPosted: Mon May 04, 2009 10:45 pm
 Reply with quote
My apologies. Thanks alot for the help.
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 -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts DFsort help with SUM() DFSORT/ICETOOL 12
No new posts DFSORT - VB file RDW getting overridden DFSORT/ICETOOL 3
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