I've tried repeatedly to follow what you provided in the first go-round. But with my actual data, it just didn't work.
.
So, I'll give the actual data here, and beg for more help. Suppose we are looking at 'marathon' results. Assume 5 'races' by hundreds of 'runners'. We only examine the top 20 finalists (but perhaps the top 30, or top 50). Here is the order they come in for the 5 races....
.
Order | Race_1 | Race_2 | Race_3 | Race_4 | Race_5 |
1 | 22604_22804 | 23401_23403 | 23401_23403 | 23401_23403 | 23401_23403 |
2 | 22402_22604 | 20506_23003 | 22604a_23005 | 22702_23003 | 20506_21402 |
3 | 22601_22604a | 22604a_23005 | 20506_23003 | 20506_21402 | 21802_23401 |
4 | 20206_21503 | 21402_23401 | 20506_21402 | 21702_23306 | 21402_21805 |
5 | 21503a_22002 | 21802_23401 | 21702_23306 | 21402_22706 | 20801_23003 |
6 | 22402_23003 | 21702_23306 | 22702_23003 | 21801_22603a | 22805_23002 |
7 | 20706_21605 | 21402_23403 | 21005_21301 | 20505_21402 | 21402_21806 |
8 | 20901_22803 | 20801_22604a | 20801_23004 | 21402_21805 | 22702_23003 |
9 | 21402_22804 | 20506_21402 | 20801_22604a | 21402_22705 | 21402_22706 |
10 | 21401_23101 | 23002_23403 | 20505_21402 | 20506_23003 | 21702_23306 |
11 | 20301_20501 | 21806_23004 | 21805_23003 | 20801_23004 | 21802_23405 |
12 | 20305_20901 | 20801_23004 | 21801_22603a | 22604a_23005 | 21801_22603a |
13 | 20301_23005 | 22301_23003 | 22604a_23002 | 22604a_23002 | 22001_23003 |
14 | 21502_21806 | 21005_21301 | 21402_22706 | 20301_21801 | 20505_21402 |
15 | 20802_21005 | 21103_21801 | 22703_23003 | 21402_21806 | 21402_21703 |
16 | 21602_22804 | 22703_23003 | 21402_23401 | 21402_22703 | 21802_23403 |
17 | 21004_21401 | 23002_23401 | 20301_21801 | 22703_23003 | 21402_22703 |
18 | 20203_22401 | 20505_21402 | 21402_22705 | 22706_23003 | 21402_22705 |
19 | 21605_23202 | 21402_22705 | 21402_23403 | 21401_21501 | 20801_21402 |
20 | 21502_22602a | 21402_23405 | 21806_23003 | 21805_23003 | 21402_23401 |
.
I need to transform the above data format to show the Runner in the first column, followed by the Order each runner comes in for the particular race....
.
Runner | Order_1 | Order_2 | Order_3 | Order_4 | Order_5 |
20506_21402 | * | * | * | 3 | 2 |
20506_23003 | * | 2 | 3 | 10 | * |
22402_22604 | 2 | * | * | * | * |
22601_22604a | 3 | * | * | * | * |
22604_22804 | 1 | * | * | * | * |
22604a_23005 | * | 3 | 2 | 12 | * |
22702_23003 | * | * | 6 | 2 | 8 |
23401_23403 | * | 1 | 1 | 1 | 1 |
.
Note that 23401_23403 placed first in the last four races, but did not even place in the top 20 in the first race.
Example 74.2 Principal Components of Basketball Rankings
.
I'll greatly appreciate your help.
.
Thanks.
.
Nicholas Kormanik
.
OK.
data input; infile cards expandtabs; input rank (race1-race5) (: $40.) ; cards; 1 22604_22804 23401_23403 23401_23403 23401_23403 23401_23403 2 22402_22604 20506_23003 22604a_23005 22702_23003 20506_21402 3 22601_22604a 22604a_23005 20506_23003 20506_21402 21802_23401 4 20206_21503 21402_23401 20506_21402 21702_23306 21402_21805 5 21503a_22002 21802_23401 21702_23306 21402_22706 20801_23003 6 22402_23003 21702_23306 22702_23003 21801_22603a 22805_23002 7 20706_21605 21402_23403 21005_21301 20505_21402 21402_21806 8 20901_22803 20801_22604a 20801_23004 21402_21805 22702_23003 9 21402_22804 20506_21402 20801_22604a 21402_22705 21402_22706 10 21401_23101 23002_23403 20505_21402 20506_23003 21702_23306 11 20301_20501 21806_23004 21805_23003 20801_23004 21802_23405 12 20305_20901 20801_23004 21801_22603a 22604a_23005 21801_22603a 13 20301_23005 22301_23003 22604a_23002 22604a_23002 22001_23003 14 21502_21806 21005_21301 21402_22706 20301_21801 20505_21402 15 20802_21005 21103_21801 22703_23003 21402_21806 21402_21703 16 21602_22804 22703_23003 21402_23401 21402_22703 21802_23403 17 21004_21401 23002_23401 20301_21801 22703_23003 21402_22703 18 20203_22401 20505_21402 21402_22705 22706_23003 21402_22705 19 21605_23202 21402_22705 21402_23403 21401_21501 20801_21402 20 21502_22602a 21402_23405 21806_23003 21805_23003 21402_23401 ; run; data _null_; if _n_ eq 1 then do; length order1 - order5 8 k $ 40; declare hash ha(ordered:'y'); ha.definekey('k'); ha.definedata('k','order1','order2','order3','order4','order5'); ha.definedone(); end; set input end=last; array o{*} order1-order5 ; array r{*} race1-race5 ; do i=1 to dim(r); if not missing(r{i}) then do; k=r{i}; rc=ha.find(); o{i}=rank; ha.replace(); call missing(of order:); end; end; if last then ha.output(dataset:'bballm'); run; proc means data=bballm noprint; output out=maxrank max=morder1 morder2 morder3 morder4 morder5 ; run; data bball; set bballm; if _n_=1 then set maxrank; array services
Ksharp
Here's the first step:
data step1;
set have;
array races (5) race1-race5;
do i= 1 to dim(races);
race=i;
Runner=race(i);
output;
end;
Keep order race runner;
run;
You can use proc transpose for the second step. If you can't get it to work, post your attempt including code.
Oh. That would not happen, why race3 4 5 have two champions ?
OK.
data input; infile cards expandtabs; input rank (race1-race5) (: $40.) ; cards; 1 22604_22804 23401_23403 23401_23403 23401_23403 23401_23403 2 22402_22604 20506_23003 22604a_23005 22702_23003 20506_21402 3 22601_22604a 22604a_23005 20506_23003 20506_21402 21802_23401 4 20206_21503 21402_23401 20506_21402 21702_23306 21402_21805 5 21503a_22002 21802_23401 21702_23306 21402_22706 20801_23003 6 22402_23003 21702_23306 22702_23003 21801_22603a 22805_23002 7 20706_21605 21402_23403 21005_21301 20505_21402 21402_21806 8 20901_22803 20801_22604a 20801_23004 21402_21805 22702_23003 9 21402_22804 20506_21402 20801_22604a 21402_22705 21402_22706 10 21401_23101 23002_23403 20505_21402 20506_23003 21702_23306 11 20301_20501 21806_23004 21805_23003 20801_23004 21802_23405 12 20305_20901 20801_23004 21801_22603a 22604a_23005 21801_22603a 13 20301_23005 22301_23003 22604a_23002 22604a_23002 22001_23003 14 21502_21806 21005_21301 21402_22706 20301_21801 20505_21402 15 20802_21005 21103_21801 22703_23003 21402_21806 21402_21703 16 21602_22804 22703_23003 21402_23401 21402_22703 21802_23403 17 21004_21401 23002_23401 20301_21801 22703_23003 21402_22703 18 20203_22401 20505_21402 21402_22705 22706_23003 21402_22705 19 21605_23202 21402_22705 21402_23403 21401_21501 20801_21402 20 21502_22602a 21402_23405 21806_23003 21805_23003 21402_23401 ; run; data _null_; if _n_ eq 1 then do; length order1 - order5 8 k $ 40; declare hash ha(ordered:'y'); ha.definekey('k'); ha.definedata('k','order1','order2','order3','order4','order5'); ha.definedone(); end; set input end=last; array o{*} order1-order5 ; array r{*} race1-race5 ; do i=1 to dim(r); if not missing(r{i}) then do; k=r{i}; rc=ha.find(); o{i}=rank; ha.replace(); call missing(of order:); end; end; if last then ha.output(dataset:'bballm'); run; proc means data=bballm noprint; output out=maxrank max=morder1 morder2 morder3 morder4 morder5 ; run; data bball; set bballm; if _n_=1 then set maxrank; array services
Ksharp
Beautiful, beautiful, beautiful!!
Thanks soooo much.
Nicholas
OK.
At the bottom of my code , You can print top 50 observations.
* Display Scores on the First Principal Component;
proc print obs=50;
var k Prin1;
run;
Yes. You can add any rows as you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.