The first TABLE FILE emulates the report I am working on, the next two TABLE FILES display the detail data:

TABLE FILE CAR
SUM MAX.RCOST
    MAX.DCOST
    AVE.RCOST
    AVE.DCOST
    CNT.RCOST
    CNT.DCOST
BY  COUNTRY
END
-*
TABLE FILE CAR
PRINT BODYTYPE
      RCOST
IF COUNTRY EQ ENGLAND
BY COUNTRY
BY RCOST NOPRINT
END
-*
TABLE FILE CAR
PRINT BODYTYPE
      DCOST
IF COUNTRY EQ 'W GERMANY'
BY COUNTRY
BY DCOST NOPRINT
END 

  
              MAX          MAX          AVE          AVE          RETAIL_COST  DEALER_COST
  COUNTRY     RETAIL_COST  DEALER_COST  RETAIL_COST  DEALER_COST  COUNT        COUNT      
  -------     -----------  -----------  -----------  -----------  -----------  -----------
  ENGLAND          17,850       14,940       11,330        9,463            4            4
  FRANCE            5,610        4,631        5,610        4,631            1            1
  ITALY            31,500       25,000       12,766       10,309            4            4
  JAPAN             3,339        2,886        3,239        2,756            2            2
  W GERMANY        14,123       11,000        9,247        7,795            7            7
  
  COUNTRY     BODYTYPE      RETAIL_COST                                         
  -------     --------      -----------                                         
  ENGLAND     HARDTOP             5,100
              CONVERTIBLE         8,878
              SEDAN              13,491
              SEDAN              17,850
  
  COUNTRY     BODYTYPE      DEALER_COST                                         
  -------     --------      -----------                                         
  W GERMANY   SEDAN               5,063
              SEDAN               5,800
              SEDAN               6,000
              SEDAN               8,300
              SEDAN               8,400
              SEDAN              10,000
              SEDAN              11,000

I need to add two new columns to the report, Median Retail Cost and Median Dealer Cost.

Given the detail data, the Median Retail Cost for England would be (8878 + 13491) / 2.

And the Median Dealer Cost for Italy would be 8300.

I've searched for median, and it seems to me that there were examples of one-off type median calculations, but no threads bringing median calcs/columns into a fully developed report.

Any comments, ideas, or suggestions welcomed.

This message has been edited. Last edited by: <Kathryn Henning>,

Hi David,

This may give a head start at calculating the median of a value. I leveraged your first TABLE FILE statement to capture base summary values:

TABLE FILE CAR
SUM
    MAX.RCOST AS 'MAX_RCOST'
    MAX.DCOST AS 'MAX_DCOST'
    AVE.RCOST AS 'AVE_RCOST'
    AVE.DCOST AS 'AVE_DCOST'
    CNT.RCOST AS 'REC_CNT'
COMPUTE C_MOD/I9 = IMOD(CNT.RCOST,2,'I9');
COMPUTE C_MEDIAN_STR/I9 = (CNT.RCOST / 2) + C_MOD;
COMPUTE C_MEDIAN_END/I9 = C_MEDIAN_STR + DECODE C_MOD (0 1 ELSE 0);
BY  COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HSUM FORMAT FOCUS INDEX COUNTRY
END
-RUN

With that information in place it's now possible to determine which records can be used to calculate the median(RCOST) for each COUNTRY:

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN HSUM AS J0

TABLE FILE CAR
PRINT 
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1;
      BODYTYPE
      RCOST
COMPUTE C_RCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN RCOST ELSE MISSING;
COMPUTE C_RCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN RCOST ELSE MISSING;
BY COUNTRY
BY RCOST NOPRINT
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT, FONT='COURIER NEW', SIZE=9, BORDER=0.5, $
TYPE=TITLE, BACKCOLOR='SILVER', $
ENDSTYLE
END
-RUN

These are the detail results illustrating each pair of RCOST values (MED1 and MED2) will be used to calculate the median by COUNTRY:

This message has been edited. Last edited by: njsden,

Here's a complete sample code that calculates the median RCOST and DCOST putting all measurements together by COUNTRY.

The real challenge here is the multiple passes required on the underlying detail data for each median that needs to be calculated.

Given that your data is hosted in an Oracle database, you may consider using SQL analytics to calculate all the measurements values directly in the database and retrieve them via SQL passthru or perhaps through a database view. Performance gains may be significant when avoiding all those multiple passes over detail data.

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

-* Base measures by country
TABLE FILE CAR
SUM
    MAX.RCOST AS 'MAX_RCOST'
    MAX.DCOST AS 'MAX_DCOST'
    AVE.RCOST AS 'AVE_RCOST'
    AVE.DCOST AS 'AVE_DCOST'
    CNT.RCOST AS 'REC_CNT'
COMPUTE C_MOD/I9 = IMOD(CNT.RCOST,2,'I9');
COMPUTE C_MEDIAN_STR/I9 = (CNT.RCOST / 2) + C_MOD;
COMPUTE C_MEDIAN_END/I9 = C_MEDIAN_STR + DECODE C_MOD (0 1 ELSE 0);
BY  COUNTRY
ON TABLE HOLD AS HSUM FORMAT FOCUS INDEX COUNTRY
END
-RUN

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN HSUM AS J0
-RUN

-* Calculate median RCOST
TABLE FILE CAR
PRINT
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1; NOPRINT
COMPUTE C_RCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN RCOST ELSE MISSING;
COMPUTE C_RCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN RCOST ELSE MISSING;
BY COUNTRY
BY RCOST NOPRINT
ON TABLE HOLD AS HRCOST
END

TABLE FILE HRCOST
SUM
COMPUTE C_RCOST_MEDIAN/D10.2 MISSING ON = (C_RCOST_MED1 + C_RCOST_MED2) / 2;
BY COUNTRY
ON TABLE HOLD AS HRCOSTMED FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* Calculate median DCOST
TABLE FILE CAR
PRINT
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1; NOPRINT
COMPUTE C_DCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN DCOST ELSE MISSING;
COMPUTE C_DCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN DCOST ELSE MISSING;
BY COUNTRY
BY DCOST NOPRINT
ON TABLE HOLD AS HDCOST
END

TABLE FILE HDCOST
SUM
COMPUTE C_DCOST_MEDIAN/D10.2 MISSING ON = (C_DCOST_MED1 + C_DCOST_MED2) / 2;
BY COUNTRY
ON TABLE HOLD AS HDCOSTMED FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* Put all measures together
JOIN CLEAR *
JOIN COUNTRY IN HSUM TO COUNTRY IN HRCOSTMED AS J0
JOIN COUNTRY IN HSUM TO COUNTRY IN HDCOSTMED AS J1
-RUN

TABLE FILE HSUM
PRINT
    MAX_RCOST
    MAX_DCOST
    AVE_RCOST
    AVE_DCOST
    REC_CNT
    C_RCOST_MEDIAN
    C_DCOST_MEDIAN
BY COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT, FONT='COURIER NEW', SIZE=9, BORDER=0.5, $
TYPE=TITLE, BACKCOLOR='SILVER', $
ENDSTYLE
END
-RUN

Hope this helps.

This message has been edited. Last edited by: njsden,

Thank you njsden, indeed you are assisting with making this thread a comprehensive example of bringing median cals into a fully developed report.

quote:

you may consider using SQL analytics to calculate

Thanks for the reminder on this. In my present case my input dataset is a flat file, created by extracts from Netezza, which is my starting point.

quote:

real challenge here is the multiple passes required on the underlying detail data for each median that needs to be calculated.

That is what I was thinking too, so it really helped to hear someone else's take.
Incorporating your technique, and median calculations presented by the FocWizards on another thread, here is what I have, that I think will work fine:

-* Focexec: ExMedian
-* Purpose: Example of calculating a median, and reporting it along with other summary type calculations.
-*
-* Environmental Settings.
-*
SET PAGE = NOPAGE
SET ONLINE-FMT = STANDARD
SET LINES = 99999
SET PANEL = 200
-*
-* Calculate and store the median RetailCost.    
-*
TABLE FILE CAR
SUM     CNT.BODYTYPE
COMPUTE M1/I4=INT((CNT.BODYTYPE + 1)/2);
COMPUTE M2/I4=INT((CNT.BODYTYPE + 2)/2);
BY      COUNTRY
-*
PRINT
COMPUTE BYFIELDS/A10 = COUNTRY;
COMPUTE XLIST/I5     = IF BYFIELDS EQ LAST BYFIELDS THEN XLIST + 1 ELSE 1;
COMPUTE CRCOST/D12.2 = IF M1 NE M2
                        THEN (LAST RCOST + RCOST)/2 ELSE RCOST; AS 'MEDIAN_RCOST'
BY      COUNTRY
BY      RCOST
-*
ON TABLE HOLD AS HLDRCMED FORMAT FOCUS INDEX COUNTRY
WHERE TOTAL XLIST EQ M2
END
-RUN
-*
-* Calculate and store the median DealerCost.    
-*
TABLE FILE CAR
SUM     CNT.BODYTYPE
COMPUTE M1/I4=INT((CNT.BODYTYPE + 1)/2);
COMPUTE M2/I4=INT((CNT.BODYTYPE + 2)/2);
BY      COUNTRY
-*
PRINT
COMPUTE BYFIELDS/A10 = COUNTRY;
COMPUTE XLIST/I5     = IF BYFIELDS EQ LAST BYFIELDS THEN XLIST + 1 ELSE 1;
COMPUTE CDCOST/D12.2 = IF M1 NE M2
                        THEN (LAST DCOST + DCOST)/2 ELSE DCOST; AS 'MEDIAN_DCOST'
BY      COUNTRY
BY      DCOST
-*
ON TABLE HOLD AS HLDDCMED FORMAT FOCUS INDEX COUNTRY
WHERE TOTAL XLIST EQ M2
END
-RUN
-*
JOIN COUNTRY IN CAR TO COUNTRY IN HLDRCMED AS J1
JOIN COUNTRY IN CAR TO COUNTRY IN HLDDCMED AS J2
-*
TABLE FILE CAR
SUM MAX.RCOST
    MAX.DCOST
    AVE.RCOST
    AVE.DCOST
    CNT.RCOST
    CNT.DCOST
    MEDIAN_RCOST    AS 'Median, Retail Cost'
    MEDIAN_DCOST    AS 'Median, Dealer Cost'
BY  COUNTRY
END 

  
              MAX          MAX          AVE          AVE          RETAIL_COST  DEALER_COST    Median          Median      
  COUNTRY     RETAIL_COST  DEALER_COST  RETAIL_COST  DEALER_COST  COUNT        COUNT           Retail Cost     Dealer Cost
  -------     -----------  -----------  -----------  -----------  -----------  -----------    ------------    ------------
  ENGLAND          17,850       14,940       11,330        9,463            4            4       11,184.50        9,310.50
  FRANCE            5,610        4,631        5,610        4,631            1            1        5,610.00        4,631.00
  ITALY            31,500       25,000       12,766       10,309            4            4        6,820.00        5,660.00
  JAPAN             3,339        2,886        3,239        2,756            2            2        3,239.00        2,756.00
  W GERMANY        14,123       11,000        9,247        7,795            7            7        9,097.00        8,300.00  

Ahhh! Multi-verb requests. They really simplify the process avoiding unnecessary intermediate HOLD file creation. Very nice!