I'm sure it's easy, but for the life of me, I can't figure this one out. I would like to calculate the percentages by the columns, not the rows.

In the results of the sample FEX below, I would like to see 50%, 25%, 25% in the "Count" percent rows for England.

I must be missing something small.

Thanks in advance,

Francis.

-* Example of PCT and CNT for a BY and ACROSS report

DEFINE FILE CAR
CTOT_COST/D8 WITH DEALER_COST = 1;
TOT_COST/D8 = DEALER_COST;
END

TABLE FILE CAR
SUM

CTOT_COST NOPRINT
TOT_COST NOPRINT

BY SEATS
SUM
CNT.DEALER_COST AS 'Count'
COMPUTE PCTCTOT_COST/D4% = CNT.DEALER_COST / CTOT_COST * 100; AS ''
DEALER_COST AS 'Cost'
COMPUTE PCTDEALER_COST/D4% = DEALER_COST / TOT_COST * 100; AS ''
BY SEATS
ACROSS COUNTRY AS ''
AND COLUMN-TOTAL

WHERE COUNTRY IN ('ENGLAND', 'ITALY');
ON TABLE SET STYLE *
TYPE=REPORT, FONT='Verdana', SIZE=9, $
TYPE=TITLE,      COLOR=GREEN, $
TYPE=DATA,       COLOR=GREEN, $
TYPE=SUBTOTAL,   COLOR=GREEN, $
TYPE=GRANDTOTAL, COLOR=GREEN, $
TYPE=TITLE,      COLUMN=DEALER_COST(*), COLOR=BLUE, $
TYPE=DATA,       COLUMN=DEALER_COST(*), COLOR=BLUE, $
TYPE=SUBTOTAL,   
COLUMN=DEALER_COST(*),    COLOR=BLUE, $
TYPE=GRANDTOTAL, COLUMN=DEALER_COST(*), COLOR=BLUE, $
TYPE=TITLE,      COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $
TYPE=DATA,       COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $
TYPE=SUBTOTAL,   COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $
TYPE=GRANDTOTAL, COLUMN=PCTDEALER_COST(*), COLOR=BLUE, $
TYPE=TITLE,      COLUMN=SEATS(*),    COLOR=BLACK, $
TYPE=DATA,       COLUMN=SEATS(*),    COLOR=BLACK, $
TYPE=SUBTOTAL,   COLUMN=SEATS(*),    COLOR=BLACK, $
TYPE=GRANDTOTAL, COLUMN=SEATS(*),    COLOR=BLACK, $
END

This message has been edited. Last edited by: Francis Mariani,

The short answer is to use the PCT.CNT. and PCT. prefix operators. See the code below:

DEFINE FILE CAR
CTOT_COST/D8 WITH DEALER_COST = 1;
END

TABLE FILE CAR
SUM
CNT.DEALER_COST AS 'Count'
PCT.CTOT_COST/D3% AS ''
-*PCT.CNT.DEALER_COST AS ''
DEALER_COST AS 'Cost'
PCT.DEALER_COST/D3% AS ''
BY SEATS
ACROSS COUNTRY AS ''
COLUMN-TOTAL
WHERE COUNTRY IN ('ENGLAND', 'ITALY');
END

Just some gotchas in regards to the code above.

I added the "/D3%" to change the format of the percent value on the fly so it shows no decimal places and adds the "%" sign. You need to decide if you would like to use "D3%" or "I3%". Each one will round appropriately to the nearest whole number. D3% will guarentee the COLUMN-TOTAL shows 100% across the board, whereas using I3% will not. Run it with both to see what I mean.

Notice the commented line in the code which used PCT.CNT. as a prefix operator to DEALER_COST. Apparently trying to reformat the field on the fly does not work when using PCT.CNT. so I went with the DEFINE field you originally had in the code.

Here are the results:

ENGLAND ITALY
SEATS Count Cost Count Cost
-------------------------------------------------------------
2 2 50% 11,719 31% 3 75% 36,320 88%
4 1 25% 14,940 39% 1 25% 4,915 12%
5 1 25% 11,194 30% 0 . . .

TOTAL 4 100% 37,853 100% 4 100% 41,235 100%


Notice the three % values for COST add up to 100% and the TOTAL line does say 100%. Using I3% would yield different individual values due to truncation (not rounding) and a total of 98%. I was wrong in my previous post when I said both D3% and I3% would round appropriately. Play with both and see what the results are.

Mickey, Thanks for your response. I avoided using the PCT prefix because I could not get it to work when there's more than one BY statement. Unfortunately the example I posted did not make that clear. The code below has more than one BY statement. The PCT. creates a column that adds up to 100 for the TOTAL of the report, I'd like to calculate the percentage of each line within the highest BY statement (BODYTYPE).

Cheers,

Francis

TABLE FILE CAR
SUM CNT.DEALER_COST AS 'Count'
PCT.CNT.DEALER_COST/D4% AS ''
DEALER_COST AS 'Cost'
PCT.DEALER_COST/D4% AS ''
BY BODYTYPE NOPRINT SUBHEAD " "
"Body Type: <BODYTYPE"
" "
BY SEATS
ACROSS COUNTRY AS ''
AND ACROSS-TOTAL
AND COLUMN-TOTAL
END

This message has been edited. Last edited by: Francis Mariani,

Look WITHIN yourself, or at least your post, and ye shall find the answer. Use the key word WITHIN to get what you are looking for. Incidentally, ACROSS-TOTAL and COLUMN-TOTAL become useless at this point. Run the code below to see what I mean.

DEFINE FILE CAR
CTOT_COST/D8 WITH DEALER_COST = 1;
END
TABLE FILE CAR
SUM
CNT.DEALER_COST AS 'Count'
PCT.CTOT_COST/D3% WITHIN BODYTYPE WITHIN COUNTRY AS ''
DEALER_COST AS 'Cost'
PCT.DEALER_COST/D3% WITHIN BODYTYPE WITHIN COUNTRY AS ''
BY BODYTYPE NOPRINT SUBHEAD
" "
"Body Type: <BODYTYPE"
" "
BY SEATS
ACROSS COUNTRY AS ''
ACROSS-TOTAL COLUMN-TOTAL
END

Mickey,

Thank you!

I've never used WITHIN in this way.

Cheers,

Francis.

Mickey,

I'm back at the PCT. WITHIN game!

Would you know why the percentages do not add up to 100% when using decimal places, i.e D8.4% rather than D3%?

Thanks.

Here's another idea without using across

DEFINE FILE CAR
T_CNT/I5SC WITH DEALER_COST = 1;
T_CNT_PCT/D5.1S WITH DEALER_COST = 1;
T_CST/D8SC = DEALER_COST;
T_CST_PCT/D5.1S = DEALER_COST;
END
-*
TABLE FILE CAR
SUM
T_CNT AS 'Count'
T_CST AS 'Cost'
SUM
T_CNT AS 'Count'
PCT.T_CNT_PCT AS 'Pct.'
T_CST AS 'Cost'
PCT.T_CST_PCT AS 'Pct.'
BY BODYTYPE
SUM
T_CNT AS 'Count'
PCT.T_CNT_PCT WITHIN BODYTYPE AS 'Pct.'
T_CST AS 'Cost'
PCT.T_CST_PCT WITHIN BODYTYPE AS 'Pct.'
BY BODYTYPE
BY SEATS
SUM
T_CNT AS 'Count'
PCT.T_CNT_PCT WITHIN SEATS AS 'Pct.'
T_CST AS 'Cost'
PCT.T_CST_PCT WITHIN SEATS AS 'Pct.'
BY BODYTYPE
BY SEATS
BY COUNTRY
END

Jim

I now realize that the column that the PCT is used on must be formatted as D99.99 for the percentages to add up to 100.

slick! i'd never used WITHIN in that way before, either. thanks mickey.

quote:

Originally posted by Francis Mariani:
I now realize that the column that the PCT is used on must be formatted as D99.99 for the percentages to add up to 100.

Francis,

You should not need to include decimal positions in your field reformat.

The key is to make sure the field on which the PCT. prefix is applied has a D (or F) format. This is because D and F format fields maintain significant digits internally even though it will not show them all.

The following is an expanded version of my original example which illustrates what I mean. Hopefully this helps clarify the behavior.

DEFINE FILE CAR
CTOT_COST/D8 WITH DEALER_COST = 1;
END

TABLE FILE CAR
SUM
CNT.DEALER_COST AS 'Count'
PCT.CTOT_COST/D6.2% AS 'D6.2%'
PCT.CTOT_COST/I6% AS 'I6%'
DEALER_COST AS 'Cost'
PCT.DEALER_COST/D6.2% AS 'D6.2%'
PCT.DEALER_COST/I6% AS 'I6%'
BY SEATS
ACROSS COUNTRY AS ''
COLUMN-TOTAL
WHERE COUNTRY IN ('ENGLAND', 'ITALY');
END

Mickey, thanks for the clarification.

My note regarding use of D99.99 was unclear - I meant zero or more decimal places in D format field.

Regards.

Hello, i'm having a similar problem.

here is what my code looks like:

SUM CNTR NOPRINT PCT.CNTR/D12.2% WITHIN TABLE AS '%' WITHIN TIER
BY CATEGORY

SUM CNT.ITEM AS 'HD count'
BY CATEGORY
BY CTYPE
BY ITEM
ACROSS SOMETHING AS ''

END


it works fine without the across and the second within, but as soon as i add it, i keep getting zeros...

please help.

Zu, "TABLE" is a reserved word
i'ld avoid it.

i was using the example in the book, i thought i was using TABLE as a reserved word... i dont have a field like that.

I am back to the PCT ACROSS BY problem FIVE years later!!!

SET PAGE=NOPAGE
DEFINE FILE CAR
REGION/A10 = IF COUNTRY IN ('ENGLAND', 'FRANCE') THEN 'REGION 1' ELSE 'REGION 2';
DSALES/D6 = SALES;
END

TABLE FILE CAR
SUM PCT.DSALES AS ''
BY REGION PAGE-BREAK
BY COUNTRY
ACROSS SEATS AS ''
END

Results:

                       2     4     5
REGION   COUNTRY
-------- ---------  ----  ----  ----
REGION 1 ENGLAND       0     0    13
         FRANCE        .     .     0

====================================

                       2     4     5
REGION   COUNTRY
-------- ---------  ----  ----  ----
REGION 2 ITALY       100     5     .
         JAPAN         .    85     .
         W GERMANY     .    10    87

I would like:

                       2     4     5
REGION   COUNTRY
-------- ---------  ----  ----  ----
REGION 1 ENGLAND       0     0   100
         FRANCE        .     .     0

====================================

                       2     4     5
REGION   COUNTRY
-------- ---------  ----  ----  ----
REGION 2 ITALY       100     5     .
         JAPAN         .    85     .
         W GERMANY     .    10   100

I thought I could control that by WITHIN, but the WITHIN field is not REGION, nor is it COUNTRY.
I get an error when I use SEATS:
FOC168) THE 'WITHIN' FIELD IS NOT ALSO A 'BY' OR 'ACROSS' FIELD:, but SEATS is an ACROSS field!

HELP!

This message has been edited. Last edited by: Francis Mariani,

If

SUM PCT.DSALES AS '' WITHIN SEATS

does not work, you might try

SUM PCT.DSALES AS '' WITHIN TABLE WITHIN SEATS

Dan,

Thanks for the suggestion, it was not completely correct but did point me to the right answer:

PCT.DSALES WITHIN REGION WITHIN SEATS AS ''

This message has been edited. Last edited by: Francis Mariani,

Unfortunately, this doesn't seem to work in the real world:

TABLE FILE R010H200
SUM

PCT.CNT_UBN_ID/D6.2S WITHIN RRS_OSFI_REF WITHIN P2_OSFI_BIRR_CD WITHIN P2_MS_RR_NAME AS ''

BY RRS_OSFI_REF     NOPRINT

BY P2_OSFI_BIRR_CD  NOPRINT
BY P2_MS_RR_NAME    AS 'Metric'

ACROSS P1_OSFI_BIRR_CD  NOPRINT
ACROSS P1_MS_RR_NAME_SHORT AS 'Final BIRR'

ON RRS_OSFI_REF     PAGE-BREAK

I get the error (FOC168) THE 'WITHIN' FIELD IS NOT ALSO A 'BY' OR 'ACROSS' FIELD

This message has been edited. Last edited by: Francis Mariani,

Below is a fex close to the real world problem I was having. The answer seems to be that the WITHIN must include the highest BY statement and only the first ACROSS statement. I found this out through trial and error.

Convoluted fex:

-*-- pct1.fex

-SET &ECHO=ALL;

-*-- Generate dummy data ---------------------------------------------

FILEDEF DATAMAST DISK R010H101.mas
FILEDEF R010H101 DISK R010H101.ftm
-RUN

-*-- Create the master for R010H101 ------------------------
-WRITE DATAMAST FILENAME=R010H101, SUFFIX=FIX, $
-WRITE DATAMAST SEGMENT=R010H101, SEGTYPE=S0, $
-WRITE DATAMAST FIELDNAME=BOR_RRS_CD      , ALIAS=E03, USAGE=A03 , ACTUAL=A03, $
-WRITE DATAMAST FIELDNAME=UBN_ID          , ALIAS=E01, USAGE=A20 , ACTUAL=A20, $
-WRITE DATAMAST FIELDNAME=P2_PERIOD_END_DT, ALIAS=E05, USAGE=YYMD, ACTUAL=A08, $
-WRITE DATAMAST FIELDNAME=P1_BOR_RR_CD    , ALIAS=E02, USAGE=A02 , ACTUAL=A02, $
-WRITE DATAMAST FIELDNAME=P2_BOR_RR_CD    , ALIAS=E04, USAGE=A02 , ACTUAL=A02, $

-*-- Create the data file for R010H101 ---------------------

-*-- Set up RRS ------------------------
-SET &NBR_RRS = 2;
-SET &BOR_RRS_CD1 = 'COM';
-SET &BOR_RRS_CD2 = 'COR';

-*-- Set up all possible Risk Ratings
-DEFAULTS &RRCD1 = '05', &RRCD2 = '10', &RRCD3 = '15', &RRCD4 = '20';
-DEFAULTS &RRCD5 = '25', &RRCD6 = '30', &RRCD7 = '35', &RRCD8 = '40';
-DEFAULTS &RRCD9 = '45', &RRCD10= '46', &RRCD11= '47', &RRCD12= '50';
-DEFAULTS &RRCD13= '51', &RRCD14= '52', &RRCD15= '60', &RRCD16= '71';
-DEFAULTS &RRCD17= '72', &RRCD18= '80', &RRCD19= '90';

-*-- Repeat loop for Risk Ratings, Period 1
-REPEAT :LOOP_B2 FOR &N FROM 1 TO 19 STEP 1;

-SET &P1_RRCD = &RRCD.&N;

-*-- Repeat loop for Risk Ratings, Period 2
-REPEAT :LOOP_B3 FOR &X FROM 1 TO 19 STEP 1;

-SET &P2_RRCD = &RRCD.&X;

-*-- Repeat loop for Risk Rating Systems
-REPEAT :LOOP_B4 FOR &Y FROM 1 TO &NBR_RRS STEP 1;

-WRITE R010H101 &BOR_RRS_CD.&Y|0000000000000000000099991231&P1_RRCD|&P2_RRCD

-:LOOP_B4

-:LOOP_B3

-:LOOP_B2

-*-- Create report

DEFINE FILE R010H101
CNT_UBN_ID/D10 = 1;

RRS_NAME/A12 =
  DECODE BOR_RRS_CD ('COR' 'Corporate', 'COM' 'Commercial' 'CM1' 'Commercial 1' ELSE '???');

-*-- Set up Risk Rating System sort field ----------------------------
RRS_OSFI_REF/A17 =
  DECODE BOR_RRS_CD ('COR' '0201 Corporate', 'COM' '0202 Commercial' 'CM1' '0207 Commercial 1' ELSE '???');

-*-- Set up Master Scale Ratings for report --------------------------

-*-- Initial Period ----------------------------------------
P1_MS_RR_NAME/A28 =
IF P1_BOR_RR_CD EQ '05' THEN 'I-1 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '10' THEN 'I-2 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '15' THEN 'I-3 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '20' THEN 'I-4 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '25' THEN 'I-5 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '30' THEN 'I-6 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '35' THEN 'I-7 (Investment Grade)    ' ELSE
IF P1_BOR_RR_CD EQ '40' THEN 'S-1 (Sub Investment Grade)' ELSE
IF P1_BOR_RR_CD EQ '45' THEN 'S-2 (Sub Investment Grade)' ELSE
IF P1_BOR_RR_CD EQ '46' THEN 'S-3 (Sub Investment Grade)' ELSE
IF P1_BOR_RR_CD EQ '47' THEN 'S-4 (Sub Investment Grade)' ELSE
IF P1_BOR_RR_CD EQ '50' THEN 'P-1 (Watch List)          ' ELSE
IF P1_BOR_RR_CD EQ '51' THEN 'P-2 (Watch List)          ' ELSE
IF P1_BOR_RR_CD EQ '52' THEN 'P-3 (Watch List)          ' ELSE
IF P1_BOR_RR_CD EQ '60' THEN 'D-1 (Watch List)          ' ELSE
IF P1_BOR_RR_CD EQ '71' THEN 'D-2 (Impaired)            ' ELSE
IF P1_BOR_RR_CD EQ '72' THEN 'D-2 (Impaired)            ' ELSE
IF P1_BOR_RR_CD EQ '80' THEN 'D-2 (Impaired)            ' ELSE
IF P1_BOR_RR_CD EQ '90' THEN 'D-2 (Impaired)            ' ELSE
'???';

P1_MS_RR_NAME_SHORT/A3 =
IF P1_BOR_RR_CD EQ '05' THEN 'I-1' ELSE
IF P1_BOR_RR_CD EQ '10' THEN 'I-2' ELSE
IF P1_BOR_RR_CD EQ '15' THEN 'I-3' ELSE
IF P1_BOR_RR_CD EQ '20' THEN 'I-4' ELSE
IF P1_BOR_RR_CD EQ '25' THEN 'I-5' ELSE
IF P1_BOR_RR_CD EQ '30' THEN 'I-6' ELSE
IF P1_BOR_RR_CD EQ '35' THEN 'I-7' ELSE
IF P1_BOR_RR_CD EQ '40' THEN 'S-1' ELSE
IF P1_BOR_RR_CD EQ '45' THEN 'S-2' ELSE
IF P1_BOR_RR_CD EQ '46' THEN 'S-3' ELSE
IF P1_BOR_RR_CD EQ '47' THEN 'S-4' ELSE
IF P1_BOR_RR_CD EQ '50' THEN 'P-1' ELSE
IF P1_BOR_RR_CD EQ '51' THEN 'P-2' ELSE
IF P1_BOR_RR_CD EQ '52' THEN 'P-3' ELSE
IF P1_BOR_RR_CD EQ '60' THEN 'D-1' ELSE
IF P1_BOR_RR_CD EQ '71' THEN 'D-2' ELSE
IF P1_BOR_RR_CD EQ '72' THEN 'D-2' ELSE
IF P1_BOR_RR_CD EQ '80' THEN 'D-2' ELSE
IF P1_BOR_RR_CD EQ '90' THEN 'D-2' ELSE
'???';

P1_OSFI_BIRR_CD/A04 =
IF P1_BOR_RR_CD EQ '05' THEN '0101' ELSE
IF P1_BOR_RR_CD EQ '10' THEN '0102' ELSE
IF P1_BOR_RR_CD EQ '15' THEN '0103' ELSE
IF P1_BOR_RR_CD EQ '20' THEN '0104' ELSE
IF P1_BOR_RR_CD EQ '25' THEN '0105' ELSE
IF P1_BOR_RR_CD EQ '30' THEN '0106' ELSE
IF P1_BOR_RR_CD EQ '35' THEN '0107' ELSE
IF P1_BOR_RR_CD EQ '40' THEN '0108' ELSE
IF P1_BOR_RR_CD EQ '45' THEN '0109' ELSE
IF P1_BOR_RR_CD EQ '46' THEN '0110' ELSE
IF P1_BOR_RR_CD EQ '47' THEN '0111' ELSE
IF P1_BOR_RR_CD EQ '50' THEN '0112' ELSE
IF P1_BOR_RR_CD EQ '51' THEN '0113' ELSE
IF P1_BOR_RR_CD EQ '52' THEN '0114' ELSE
IF P1_BOR_RR_CD EQ '60' THEN '0115' ELSE
IF P1_BOR_RR_CD EQ '71' THEN '0116' ELSE
IF P1_BOR_RR_CD EQ '72' THEN '0116' ELSE
IF P1_BOR_RR_CD EQ '80' THEN '0116' ELSE
IF P1_BOR_RR_CD EQ '90' THEN '0116' ELSE
'???';

-*-- Final Period ------------------------------------------
P2_MS_RR_NAME/A28 =
IF (P2_PERIOD_END_DT LT '2009-01-01' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN 'No longer rated' ELSE
IF P2_BOR_RR_CD EQ '05' THEN 'I-1 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '10' THEN 'I-2 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '15' THEN 'I-3 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '20' THEN 'I-4 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '25' THEN 'I-5 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '30' THEN 'I-6 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '35' THEN 'I-7 (Investment Grade)    ' ELSE
IF P2_BOR_RR_CD EQ '40' THEN 'S-1 (Sub Investment Grade)' ELSE
IF P2_BOR_RR_CD EQ '45' THEN 'S-2 (Sub Investment Grade)' ELSE
IF P2_BOR_RR_CD EQ '46' THEN 'S-3 (Sub Investment Grade)' ELSE
IF P2_BOR_RR_CD EQ '47' THEN 'S-4 (Sub Investment Grade)' ELSE
IF P2_BOR_RR_CD EQ '50' THEN 'P-1 (Watch List)          ' ELSE
IF P2_BOR_RR_CD EQ '51' THEN 'P-2 (Watch List)          ' ELSE
IF P2_BOR_RR_CD EQ '52' THEN 'P-3 (Watch List)          ' ELSE
IF P2_BOR_RR_CD EQ '60' THEN 'D-1 (Watch List)          ' ELSE
IF P2_BOR_RR_CD EQ '71' THEN 'D-2 (Impaired)            ' ELSE
IF P2_BOR_RR_CD EQ '72' THEN 'D-2 (Impaired)            ' ELSE
IF P2_BOR_RR_CD EQ '80' THEN 'D-2 (Impaired)            ' ELSE
IF P2_BOR_RR_CD EQ '90' THEN 'D-2 (Impaired)            ' ELSE
'???';

P2_MS_RR_NAME_SHORT/A15 =
IF (P2_PERIOD_END_DT LT '2009-01-01' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN 'No longer rated' ELSE
IF P2_BOR_RR_CD EQ '05' THEN 'I-1' ELSE
IF P2_BOR_RR_CD EQ '10' THEN 'I-2' ELSE
IF P2_BOR_RR_CD EQ '15' THEN 'I-3' ELSE
IF P2_BOR_RR_CD EQ '20' THEN 'I-4' ELSE
IF P2_BOR_RR_CD EQ '25' THEN 'I-5' ELSE
IF P2_BOR_RR_CD EQ '30' THEN 'I-6' ELSE
IF P2_BOR_RR_CD EQ '35' THEN 'I-7' ELSE
IF P2_BOR_RR_CD EQ '40' THEN 'S-1' ELSE
IF P2_BOR_RR_CD EQ '45' THEN 'S-2' ELSE
IF P2_BOR_RR_CD EQ '46' THEN 'S-3' ELSE
IF P2_BOR_RR_CD EQ '47' THEN 'S-4' ELSE
IF P2_BOR_RR_CD EQ '50' THEN 'P-1' ELSE
IF P2_BOR_RR_CD EQ '51' THEN 'P-2' ELSE
IF P2_BOR_RR_CD EQ '52' THEN 'P-3' ELSE
IF P2_BOR_RR_CD EQ '60' THEN 'D-1' ELSE
IF P2_BOR_RR_CD EQ '71' THEN 'D-2' ELSE
IF P2_BOR_RR_CD EQ '72' THEN 'D-2' ELSE
IF P2_BOR_RR_CD EQ '80' THEN 'D-2' ELSE
IF P2_BOR_RR_CD EQ '90' THEN 'D-2' ELSE
'???';

P2_OSFI_BIRR_CD/A04 =
IF (P2_PERIOD_END_DT LT '2009-01-01' AND P2_BOR_RR_CD LT '60') OR P2_BOR_RR_CD IN ('','48') THEN '2000' ELSE
IF P2_BOR_RR_CD EQ '05' THEN '0101' ELSE
IF P2_BOR_RR_CD EQ '10' THEN '0102' ELSE
IF P2_BOR_RR_CD EQ '15' THEN '0103' ELSE
IF P2_BOR_RR_CD EQ '20' THEN '0104' ELSE
IF P2_BOR_RR_CD EQ '25' THEN '0105' ELSE
IF P2_BOR_RR_CD EQ '30' THEN '0106' ELSE
IF P2_BOR_RR_CD EQ '35' THEN '0107' ELSE
IF P2_BOR_RR_CD EQ '40' THEN '0108' ELSE
IF P2_BOR_RR_CD EQ '45' THEN '0109' ELSE
IF P2_BOR_RR_CD EQ '46' THEN '0110' ELSE
IF P2_BOR_RR_CD EQ '47' THEN '0111' ELSE
IF P2_BOR_RR_CD EQ '50' THEN '0112' ELSE
IF P2_BOR_RR_CD EQ '51' THEN '0113' ELSE
IF P2_BOR_RR_CD EQ '52' THEN '0114' ELSE
IF P2_BOR_RR_CD EQ '60' THEN '0115' ELSE
IF P2_BOR_RR_CD EQ '71' THEN '0116' ELSE
IF P2_BOR_RR_CD EQ '72' THEN '0116' ELSE
IF P2_BOR_RR_CD EQ '80' THEN '0116' ELSE
IF P2_BOR_RR_CD EQ '90' THEN '0116' ELSE
'9000';
END
-RUN

TABLE FILE R010H101
SUM
-* This gives a percentage that makes no sense
-* PCT.CNT_UBN_ID/D6.2 AS ''

-* This gives a percentage that makes no sense
-* PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF AS ''

-* This works when calculating the percentage across
-* PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD AS ''

-* This works when calculating the percentage across
-* PCT.CNT_UBN_ID/D6.2 WITHIN P2_MS_RR_NAME_SHORT AS ''

-* This works when calculating the percentage down a column
   PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF WITHIN P1_OSFI_BIRR_CD AS ''

BY RRS_OSFI_REF     NOPRINT

-*-- Final Period ------------
BY P2_OSFI_BIRR_CD  NOPRINT
BY P2_MS_RR_NAME_SHORT    AS 'Metric'

-*-- Initial Period ----------
ACROSS P1_OSFI_BIRR_CD  NOPRINT
ACROSS P1_MS_RR_NAME_SHORT AS 'Initial BIRR'

ON RRS_OSFI_REF     PAGE-BREAK

HEADING
"<RRS_NAME"
END
-RUN

Hi Francis.

Have you cooled yourself down yet? I want to thank you for this post that I learned what the PCT actually does.

Here is what I observed by changing your matrix from 19 X 19 to 3 X 4:

TABLE FILE R010H101
SUM
-* This gives a percentage that makes no sense 
-* % over the final column total (by default)
PCT.CNT_UBN_ID/D6.2 AS ''

-* This gives a percentage that makes no sense 
-* % over the by-field total
PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF AS ''

-* This works when calculating the percentage across 
-* % over by-field total(WITHIN higher by-field is assumed)
PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD AS ''

-* This works when calculating the percentage across
-* (same as the third)
PCT.CNT_UBN_ID/D6.2 WITHIN P2_MS_RR_NAME_SHORT AS ''

-* This works when calculating the percentage down a column 
-* % over the across-total(WITHIN the higher by-field)
PCT.CNT_UBN_ID/D6.2 WITHIN RRS_OSFI_REF WITHIN P1_OSFI_BIRR_CD AS ''

-* %over itself( the cell defined by P2_OSFI_BIRR_CD & P1_OSFI_BIRR_CD)
PCT.CNT_UBN_ID/D6.2 WITHIN P2_OSFI_BIRR_CD WITHIN P1_OSFI_BIRR_CD AS ''

BY RRS_OSFI_REF     NOPRINT

-*-- Final Period ------------
BY P2_OSFI_BIRR_CD  NOPRINT
BY P2_MS_RR_NAME_SHORT    AS 'Metric'

-*-- Initial Period ----------
ACROSS P1_OSFI_BIRR_CD  NOPRINT
ACROSS P1_MS_RR_NAME_SHORT AS 'Initial BIRR'

ON RRS_OSFI_REF     PAGE-BREAK

HEADING
"<RRS_NAME"
END
-RUN  

Hua

Hua,

This PCT thing really got me worked up!

After finding the solution by adding an additional WITHIN of the higher-level BY PAGE-BREAK column, I modified my program to not require the higher-level BY PAGE-BREAK column - the PCT WITHIN failed when I removed this non-required column! I had to keep it in there to make it work. The manual touches very briefly on WITHIN and it doesn't provide an example for ACROSS.

Cheers,

Back at it!

In 2005, Mickey said

quote:

The key is to make sure the field on which the PCT. prefix is applied has a D (or F) format.

That's what I'm doing and I don't get the percentages to add up to 100, even when I have four decimal places (D8.4).

PCT with WITHIN and ACROSS and BY just does not add up!

A strange discovery:

PCT.CNT_PIN/D15.3 WITHIN GROUP_CD AS 'PCT_CNT'

If the number of decimal places is even, the percentage calculation rounds up - giving me a total greater than 100%; If the number of decimal places is odd, the percentage calculation rounds down - giving me a total less than 100%.