topic Re: Adding a few Ranking variables based on the value of another variable in SAS Programming

Adding a few Ranking variables based on the value of another variable

zimcom — Sun, 12 Dec 2021 23:00:10 GMT

Greeting!

SAS Community

What I have

Site	A_N	A_Median	B_N	B_Median	C_N	C_Median
1	30	246.5	30	64.0	30	43.0
2	5	245.0	5	99.0	5	68.0
4	1	118.0	1	73.0	1	57.0
6	2	261.5	2	40.5	2	118.0
7	6	342.0	6	53.0	10	35.0
8	9	230.0	9	75.0	9	59.0
12	15	285.0	15	75.0	13	64.0
13	4	246.5	4	109.0	4	127.0
14	4	254.5	4	63.0	5	78.0
21	1	681.0	1	39.0	1	32.0
22	1	153.0	1	153.0	2	74.0
26	2	152.5	2	73.5	2	70.0
28	7	186.0	7	45.0	7	30.0
30	2	406.0	2	102.5	4	46.5
37	6	175.0	6	41.5	6	28.5
40	1	138.0	1	58.0	1	43.0
51	1	205.0	1	111.0	1	74.0
52	3	284.0	3	78.0	3	66.0
72	4	235.5	4	70.5	4	57.5
74	2	333.5	2	66.0	2	57.5
80	2	254.0	2	62.0	2	60.0
88	1	415.0	1	104.0	1	73.0
96	4	276.5	4	114.0	4	88.5

What I want

Site	A_N	A_Median	A_Rank	B_N	B_Median	B_Rank	C_N	C_Median	C_Rank
37	6	175	1	6	41.5	1	6	28.5	1
28	7	186	2	7	45	2	7	30	2
8	9	230	3	9	75	7	9	59	8
72	4	235.5	4	4	70.5	6	4	57.5	7
2	5	245	5	5	99	10	5	68	11
1	30	246.5	6	30	64	5	30	43	5
13	4	246.5	6	4	109	11	4	127	14
14	4	254.5	8	4	63	4	5	78	12
96	4	276.5	9	4	114	12	4	88.5	13
12	15	285	11	15	75	7	13	64	9
7	6	342	12	6	53	3	10	35	4
51	1	205	-	1	111	-	1	74	-
80	2	254	-	2	62	-	2	60	-
6	2	261.5	-	2	40.5	-	2	118	-
52	3	284	-	3	78	-	3	66	-
74	2	333.5	-	2	66	-	2	57.5	-
4	1	118	-	1	73	-	1	57	-
21	1	681	-	1	39	-	1	32	-
22	1	153	-	1	153	-	2	74	-
26	2	152.5	-	2	73.5	-	2	70	-
30	2	406	-	2	102.5	-	4	46.5	6
40	1	138	-	1	58	-	1	43	-
88	1	415	-	1	104	-	1	73	-

to add a ranking variables A_Rank for any observation if A_N > 3, then assign a ranking score based on the A_Median value, the smallest A_Median will be assigned to 1 and asending up, the example above was sorted on A_Rank to show the idea.

Will do the same for B_N/B_Median and C_N/C_Meidan.

I know I can do it through splitting the dataset into A, B and C and use sort and use +1 notation, and merge the dataset again.

But that is not efficient and want to know what other solution would work without splitting the dataset.

Thank you so much!

Re: Adding a few Ranking variables based on the value of another variable

ChrisNZ — Mon, 13 Dec 2021 05:34:56 GMT

You've been around for a while now, surely you know that the best way to get help is to provide data in a usable format?

Re: Adding a few Ranking variables based on the value of another variable

Ksharp — Mon, 13 Dec 2021 12:34:41 GMT

data have;
infile cards expandtabs;
input Site A_N A_Median B_N B_Median C_N C_Median;

_A_Median=A_Median;if A_N < 4 then call missing(_A_Median);
_B_Median=B_Median;if B_N < 4 then call missing(_B_Median);
_C_Median=C_Median;if C_N < 4 then call missing(_C_Median);

cards;
1 30 246.5 30 64.0 30 43.0
2 5 245.0 5 99.0 5 68.0
4 1 118.0 1 73.0 1 57.0
6 2 261.5 2 40.5 2 118.0
7 6 342.0 6 53.0 10 35.0
8 9 230.0 9 75.0 9 59.0
12 15 285.0 15 75.0 13 64.0
13 4 246.5 4 109.0 4 127.0
14 4 254.5 4 63.0 5 78.0
21 1 681.0 1 39.0 1 32.0
22 1 153.0 1 153.0 2 74.0
26 2 152.5 2 73.5 2 70.0
28 7 186.0 7 45.0 7 30.0
30 2 406.0 2 102.5 4 46.5
37 6 175.0 6 41.5 6 28.5
40 1 138.0 1 58.0 1 43.0
51 1 205.0 1 111.0 1 74.0
52 3 284.0 3 78.0 3 66.0
72 4 235.5 4 70.5 4 57.5
74 2 333.5 2 66.0 2 57.5
80 2 254.0 2 62.0 2 60.0
88 1 415.0 1 104.0 1 73.0
96 4 276.5 4 114.0 4 88.5
;

proc rank data=have out=want ties=dense;
var  _A_Median  _B_Median _C_Median ;
ranks   A_rank  B_rank C_rank  ;
run;

Re: Adding a few Ranking variables based on the value of another variable

zimcom — Mon, 13 Dec 2021 16:16:12 GMT

Thanks, I really appreciated your help!