I have a set of data columns that I'd like to rank and then store the rank. A sample is below.

Industries Rtns MV% Rank Rtns Rank MV%
Auto 5% 25% 1 2
Wireless 4% 50% 2 1
Airlines -5% 20% 4 3
Cash 1% 5% 3 4

I have the first 3 columns in by data structure and would like to be able to rank and then store the last 2 columns and then be able to report on that.

Does anyone have any thoughts?

I would
RANK Rtns and HOLD,
RANK MV% and HOLD,
MATCH the two HOLD files.

SET ASNAMES = ON
TABLE FILE CAR
SUM
SALES
DEALER_COST
BY COUNTRY
ON TABLE HOLD AS HOLD0
END
TABLE FILE HOLD0
SUM COUNTRY
RANKED BY SALES
ON TABLE HOLD AS HOLD01
END
?FF HOLD01
TABLE FILE HOLD0
SUM COUNTRY
RANKED BY DEALER_COST
ON TABLE HOLD AS HOLD02
END
?FF HOLD02
MATCH FILE HOLD01
PRINT
SALES
RANK AS 'RANK1'
BY COUNTRY
RUN
FILE HOLD02
PRINT
DEALER_COST
RANK AS 'RANK2'
BY COUNTRY
AFTER MATCH HOLD AS HOLD03 OLD-OR-NEW
END
TABLE FILE HOLD03
PRINT *
END

Francis,

Thanks for the thoughts. I haven't had time to get back to this. Seems like that code should work. Trouble could be is there are probably 20 fields or more that need to be ranked so it could be quite a bit of hold files. Will be testing in the coming months and will post any additional questions/thoughts as I go.

Ryan

use the LIST verb
it puts the ranks in the hold file as an integer

Ryan,


If you are accepting the various fields to be ranked via an HTML form and you have a mulit select (or even if you mimic it) you could use the following code to effect your variable ranking requirement -

-SET &ECHO='ALL';
SET HOLDLIST = PRINTONLY
-DEFAULT &Ranking  = RETAIL_COST
-DEFAULT &Ranking0 = 4
-DEFAULT &Ranking1 = RETAIL_COST
-DEFAULT &Ranking2 = DEALER_COST
-DEFAULT &Ranking3 = SALES
-DEFAULT &Ranking4 = SEATS 
TABLE FILE CAR
SUM 
-SET &Cnt = 1;
-REPEAT :Loop0 &Ranking0 TIMES;
    &Ranking&Cnt.EVAL/D15
-SET &Cnt = &Cnt + 1;
-:Loop0
BY MODEL
BY BODYTYPE
ON TABLE HOLD AS MYDATA
END
-RUN

-SET &Cnt = 1;
-REPEAT :Loop1 &Ranking0 TIMES;
TABLE FILE MYDATA
SUM &Ranking&Cnt.EVAL/D15
RANKED BY HIGHEST &Ranking&Cnt.EVAL
BY MODEL
BY BODYTYPE
ON TABLE HOLD AS HOLD0&Cnt.EVAL
END
-SET &Cnt = &Cnt + 1;
-:Loop1

-SET &Cnt = 1;
MATCH FILE HOLD0&Cnt.EVAL
PRINT &Ranking&Cnt.EVAL
      RANK AS 'RANK&Cnt.EVAL'
BY MODEL
BY BODYTYPE
RUN
-SET &ThisLoop = &Ranking0 - 2;
-SET &Cnt = &Cnt + 1;
-REPEAT :Loop2 &ThisLoop TIMES;
FILE HOLD0&Cnt.EVAL
PRINT &Ranking&Cnt.EVAL
      RANK AS 'RANK&Cnt.EVAL'
BY MODEL
BY BODYTYPE
RUN
-SET &Cnt = &Cnt + 1;
-:Loop2
FILE HOLD0&Cnt.EVAL
PRINT &Ranking&Cnt.EVAL
      RANK AS 'RANK&Cnt.EVAL'
BY MODEL
BY BODYTYPE
AFTER MATCH HOLD AS FINALFILE OLD-OR-NEW
END
TABLE FILE FINALFILE
PRINT *
END

You can change the DEFAULT variables to any number you want and the rest of the code remains the same. You just have to be careful that the summation of the data fields are at the same level to obtain the correct results.

T

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

Forgot to mention that it is the same technique as per Francis but using the power of dialogue manager for the variance.

It also must have 2 or more rankings to perform.

One more thing -


If you want to resort the report by clicking on the ranking column then add a DEFAULT for &SORT to RANK1 and replace the final TABLE request with this -

TABLE FILE FINALFILE
PRINT *
BY &SORT NOPRINT
ON TABLE SET STYLE *
-SET &Cnt = 1;
-REPEAT :Loop3 &Ranking0 TIMES;
  TYPE=TITLE, COLUMN=RANK&Cnt.EVAL, FOCEXEC=dynam_rank(SORT='RANK&Cnt.EVAL'), $
-SET &Cnt = &Cnt + 1;
-:Loop3
END

and remember to evaluate the amount of data that you will handle each pass so that your DBAs don't moan!!!!

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