Has anyone done this before?
I need to return only the top 10 % of distinct site ID's based on their "volume" (of activities) since the beginning of the year.

Thank you for any help!

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

For that to work, you first need to know how many there are. Then divide that amount by 10 and use that as your recodr limit.

Would you use the first TABLE FILE request to get the number of records and then continue with the rest? I thought there may be a more "compact" technique using the TOTAL lines, etc...

Something like this?

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD
END
-*
-SET &NUMLINES = &LINES*.10;

TABLE FILE CAR
PRINT * 
BY HIGHEST &NUMLINES.EVAL MODEL NOPRINT
END

Guys, this one is related volume. May not be counts.

How aboput this. It takes the top n pct, + any thay flow over the limit.

-SET &PCT_AMT = 50 ;
SET ASNAMES = ON
TABLE FILE CAR
SUM SALES AS TOTSALES
SUM SALES
BY CAR
ON TABLE HOLD AS TMP_TOP
END

TABLE FILE TMP_TOP
PRINT SALES
      COMPUTE INCSALES/I6   = LAST INCSALES + SALES ;
      COMPUTE PCTSALES/D6.2 = INCSALES / TOTSALES * 100 ;
      COMPUTE Filter/A1     = IF PCTSALES LT &PCT_AMT THEN 'Y' ELSE 
                              IF LAST Filter EQ 'Y' AND
                                 LAST PCTSALES LT &PCT_AMT
                              THEN 'Y' ELSE 'N' ;
BY HIGHEST SALES
BY CAR
WHERE TOTAL Filter EQ 'Y'
END

Similar to Waz's post, something like this might work. Your WHERE clauses would filter on your date column. I had to take the top 15% (PERCENTILE > 85) in order to get more than one country to show in the output.

SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
 SITE_SALES/D7 = SALES ;
END

TABLE FILE CAR
 SUM SITE_SALES
 BY COUNTRY

 SUM SALES/D7
 BY COUNTRY
 BY CAR
 WHERE COUNTRY NE 'FRANCE';
 ON TABLE HOLD
END

TABLE FILE HOLD
 SUM COMPUTE TOP_SALES/D7 = MAX.SITE_SALES ; NOPRINT

 PRINT
  COUNTRY AS 'Site'
  CAR     AS 'ID'
  SALES   AS 'Sales'
  COMPUTE PERCENTILE/D6.1 = SITE_SALES / TOP_SALES * 100 ; NOPRINT
  COMPUTE TOTALTEXT/A25 = 'TOTAL ' | COUNTRY ; NOPRINT
 BY HIGHEST SITE_SALES NOPRINT
 BY HIGHEST SALES      NOPRINT
 ON SITE_SALES SUBFOOT
  "<TOTALTEXT<SITE_SALES"
  "PERCENTILE<PERCENTILE"
  " <+0> "
 WHERE TOTAL (PERCENTILE GT 85)
 ON TABLE SET STYLE *
  TYPE=SUBFOOT, HEADALIGN=BODY, $
  TYPE=SUBFOOT, ITEM=1, COLSPAN=2, $
  TYPE=SUBFOOT, ITEM=2, JUSTIFY=RIGHT, $
 ENDSTYLE
END

What about IN-GROUPS-OF ... TILES? Check out the doc on this. I've never used it but it sounds like it could work.

This code will tile MODEL sales in deciles, and you can limit the output to the top decile (top 10%) by adding TOP 1 to the IN-GROUPS-OF statement. But I have never understood how the tiles are calculated. This code generates only 8 tiles, not 10. Maybe someone else can shed some light on this.

SET BYDISPLAY = ON

TABLE FILE CAR
 SUM 
  COUNTRY
  CAR
  MODEL
  SALES/D7
 BY HIGHEST SALES NOPRINT
 IN-GROUPS-OF 10 TILES
-* IN-GROUPS-OF 10 TILES TOP 1
 BY MODEL   NOPRINT
 BY CAR     NOPRINT
 BY COUNTRY NOPRINT
 ON TABLE COLUMN-TOTAL
END