Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

Posted: Mon May 04, 2009 9:00 pm    Post subject: DFSORT - percentages

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

William Thompson

Global Moderator

Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

 Posted: Mon May 04, 2009 9:10 pm    Post subject: What formula are you using for the percentage? Have you looked at TRAILER3?
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

 Posted: Mon May 04, 2009 9:14 pm    Post subject: Reply to: DFSORT - percentages Yes, i have looked at trailer3. The formula is (budget amount - actual amount) * 100 / variance. variance = budget amount - actual amount.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

 Posted: Mon May 04, 2009 9:36 pm    Post subject: 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?
Skolusu

Senior Member

Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

Posted: Mon May 04, 2009 9:39 pm    Post subject: Re: Reply to: DFSORT - percentages

 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
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

 Posted: Mon May 04, 2009 9:40 pm    Post subject: Reply to: DFSORT - percentages totaling on a percentage will not give the correct results.
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

 Posted: Mon May 04, 2009 9:45 pm    Post subject: Reply to: DFSORT - percentages I think either formual yeild the same results.
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

 Posted: Mon May 04, 2009 10:32 pm    Post subject: Reply to: DFSORT - percentages I guess i stumped you guys ?
Skolusu

Senior Member

Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

Posted: Mon May 04, 2009 10:34 pm    Post subject:

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)))  /*
Skolusu

Senior Member

Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

Posted: Mon May 04, 2009 10:35 pm    Post subject: Re: Reply to: DFSORT - percentages

 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
jr3011

New User

Joined: 17 Apr 2008
Posts: 26
Location: Oviedo, FL

 Posted: Mon May 04, 2009 10:45 pm    Post subject: Reply to: DFSORT - percentages My apologies. Thanks alot for the help.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Formatting using DFsort Guru Nandu DFSORT/ICETOOL 5 Thu Nov 30, 2017 7:33 am Compare VSAM files using DFSORT pshongal DFSORT/ICETOOL 5 Thu Nov 23, 2017 10:24 am Compare and COPY using DFSORT pshongal DFSORT/ICETOOL 3 Fri Nov 17, 2017 9:49 am Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us