topic Re: Rank by total in SAS Programming

Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 06:51:38 GMT

proc rank data=olympics_ranks out=ran ties=low;
var silver ;
ranks rankposition;
by Team/NOC notsorted;
run;


data olympics;
infile datalines dlm='/';
input Team/NOC:& $26.  Gold 29 Silver 31  Bronze 34;
datalines;
United States of America	 8	3	8
Japan						 8	2	3
People's Republic of China	 7	5	7
ROC							 5	7	3
Great Britain				 4	5	1
Australia					 3	1	3
Republic of Korea			 3	0	4
Kosovo						 2	0	0
Italy						 1	4	4
Canada						 1	3	1
France						 1	2	2
Hungary						 1	1	0
Tunisia						 1	1	0
Croatia						 1	0	1
Slovenia					 1	0	1
Austria						 1	0	0
Bermuda						 1	0	0
Ecuador						 1	0	0
Hong Kong, China			 1	0	0
Islamic Republic of Iran	 1	0	0
Norway						 1	0	0
Philippines					 1	0	0
Thailand					 1	0	0
Uzbekistan					 1	0	0
Netherlands					 0	3	0
Brazil						 0	2	2
Chinese Taipei				 0	2	2
Georgia						 0	2	0
Czech Republic				 0	1	1
Spain						 0	1	1
Indonesia					 0	1	1
Serbia						 0	1	1
Switzerland					 0	1	1
Belgium						 0	1	0
Bulgaria					 0	1	0
Colombia					 0	1	0
Denmark						 0	1	0
India						 0	1	0
Jordan						 0	1	0
Romania						 0	1	0
South Africa				 0	1	0
Germany						 0	0	3
Kazakhstan					 0	0	3
Ukraine						 0	0	3
Egypt						 0	0	2
Mongolia					 0	0	2
Turkey						 0	0	2
Côte d'Ivoire				 0	0	1
Estonia						 0	0	1
Israel						 0	0	1
Kuwait						 0	0	1
Mexico						 0	0	1
New Zealand					 0	0	1
;
run;

Required output

rank and total rank in olympics dataset

Re: Rank by total

andreas_lds — Tue, 27 Jul 2021 07:03:29 GMT

Please verify that the code you have posted runs without any errors.

How is "total" defined? Just sum of Gold, Silver and Bronze?

You could calculate "total" in a data step before calling proc rank.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 07:22:39 GMT

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 07:44:57 GMT

 73         data olympics;
 74         infile datalines dlm='/';
 75         input Team/NOC:& $26.  Gold 29 Silver 31  Bronze 34;
 76         datalines;
 
 NOTE: Invalid data for Team in line 77 1-80.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 78         Japan            8  2 3
 NOTE: Invalid data errors for file CARDS occurred outside the printed range.
 NOTE: Increase available buffer lines with the INFILE n= option.
 Team=. NOC=Japan            8  2 3 Gold=. Silver=. Bronze=. _ERROR_=1 _N_=1
 NOTE: Invalid data for Team in line 79 1-80.
 80         ROC              5  7 3
 NOTE: Invalid data errors for file CARDS occurred outside the printed range.
 NOTE: Increase available buffer lines with the INFILE n= option.
 Team=. NOC=ROC              5  7 3 Gold=. Silver=. Bronze=. _ERROR_=1 _N_=2
 NOTE: Invalid data for Team in line 81 1-80.
 82         Australia          3  1 3

Fix that first.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 08:32:50 GMT

proc import datafile ='/home/u35263349/My_Folder/Olympics.xlsx'
out=ranks_total_by_country
dbms=xlsx;
run;

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 08:42:09 GMT

Create a sum of gold,silver,bronze for each observation
Run PROC RANK with TIES=LOW and DESCENDING for the total variable
Sort by the variable named in the RANKS Statement

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 08:44:23 GMT

Fix the data step and keep the useless Excel where it belongs (a place where the sun never shines).

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 09:37:06 GMT

data olympics;
input @1Team $26. @27Gold 28. @29Silver 29. @31Bronze 31.;
datalines;
United States of America 8 3 8
Japan 8 2 3
People's Republic of China 7 5 7
ROC 5 7 3
Great Britain 4 5 1
Australia 3 1 3
Republic of Korea 3 0 4
Kosovo 2 0 0
Italy 1 4 4
Canada 1 3 1
France 1 2 2
Hungary 1 1 0
Tunisia 1 1 0
Croatia 1 0 1
Slovenia 1 0 1
Austria 1 0 0
Bermuda 1 0 0
Ecuador 1 0 0
Hong Kong, China 1 0 0
Islamic Republic of Iran 1 0 0
Norway 1 0 0
Philippines 1 0 0
Thailand 1 0 0
Uzbekistan 1 0 0
Netherlands 0 3 0
Brazil 0 2 2
Chinese Taipei 0 2 2
Georgia 0 2 0
Czech Republic 0 1 1
Spain 0 1 1
Indonesia 0 1 1
Serbia 0 1 1
Switzerland 0 1 1
Belgium 0 1 0
Bulgaria 0 1 0
Colombia 0 1 0
Denmark 0 1 0
India 0 1 0
Jordan 0 1 0
Romania 0 1 0
South Africa 0 1 0
Germany 0 0 3
Kazakhstan 0 0 3
Ukraine 0 0 3
Egypt 0 0 2
Mongolia 0 0 2
Turkey 0 0 2
Côte d'Ivoire 0 0 1
Estonia 0 0 1
Israel 0 0 1
Kuwait 0 0 1
Mexico 0 0 1
New Zealand 0 0 1
;
run;



kindly help to solve my problem

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 10:32:24 GMT

Copy/paste the code to your SAS environment, run it, study the log and look at the dataset.

And then FIX IT!

If, after 450+ posts here, you cannot get this very simple and basic thing straight, then it's hopeless and you're just wasting time trying to learn something that is much too demanding for you.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 10:37:28 GMT

please give solution

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 10:49:19 GMT

Any solution starts with correct data. As long as we don't have that, there's no sense in wasting time developing code for crap.

I repeat: if, after all the effort we spent in trying to teach you, you still cannot fix a simple data step issue, then you are only wasting time. Yours and ours.

Show us that you have what it takes to work successfully with computers. Both in terms of brains and the willingness to use them.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 11:53:35 GMT

proc import datafile='/home/u35263349/My_Folder/Olympics.xlsx'
out=ranking_olympics
dbms=xlsx;
run;

proc sort data=ranking_olympics;
by  descending gold silver bronze;
run;

proc summary data=ranking_olympics nway;
class team;
var gold silver bronze;
output out=groupsum (drop=_:) sum=;
run;

I am using proc import to create dataset I already shared excel sheet

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 12:12:49 GMT

Do what I told you here

No sorting before needed. Just create the Total variable and then run PROC RANK as described. After that, sort by rank, and you get what you want.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 12:31:12 GMT

but in my proc summar did not sum three variables why

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 12:50:45 GMT

PROC SUMMARY is for vertical sums, not horizontal. Just do

total = sum(.....);

in a data step. You do not need SUMMARY at all for your task. You would need it if you had a separate observation for gold, silver and bronze for each nation, like this:

data olymp;
input nation $ medal $ number;
datalines;
USA gold 5
USA silver 3
USA bronze 9
;

All you need is a data step to build Total, a PROC RANK step, and a SORT step to sort the nations by rank.

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 12:58:32 GMT

I didnot get output plese give solution

Re: Rank by total

Kurt_Bremser — Tue, 27 Jul 2021 13:07:31 GMT

Write the data step to create Total.

Write a PROC RANK step, according to the documentation.

Sort the result.

It's that easy.

Listen, Anand: I want you to LEARN something, and develop the ability to solve problems on your own. You will not learn ANYTHING if all you do is copy the code we give you, without understanding what's going on. Learning by doing is the best method by far, so DO!

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 14:52:52 GMT

proc import datafile='/home/u35263349/My_Folder/Olympics.xlsx'
out=ds
dbms=xlsx;
run;

data ds1;
set ds;
Total=sum(gold ,silver, bronze);
proc print;
run;

proc rank data=ds1 out=ranking  descending ties=dense;
var Total ;
ranks  total_by_ranks ;
run;

OLYMPICS OUTPUT;

Team	Gold	Silver	Bronze	Total	Total_by_Rank
Japan	10	3	5	18	3
United States of America	9	8	8	25	1
People's Republic of China	9	5	7	21	2
ROC	7	7	4	18	3
Great Britain	4	5	4	13	5
Republic of Korea	3	2	5	10	7
Australia	3	1	5	9	8
Canada	2	3	3	8	9
France	2	2	3	7	10
Germany	2	0	3	5	12
Kosovo	2	0	0	2	22
Italy	1	5	6	12	6
Chinese Taipei	1	2	3	6	11
Brazil	1	2	2	5	12
Switzerland	1	2	2	5	12
Slovenia	1	1	1	3	15
Serbia	1	1	1	3	15
Hungary	1	1	0	2	22
Tunisia	1	1	0	2	22
Austria	1	0	1	2	22
Croatia	1	0	1	2	22
Estonia	1	0	1	2	22
Bermuda	1	0	0	1	36
Ecuador	1	0	0	1	36
Hong Kong, China	1	0	0	1	36
Islamic Republic of Iran	1	0	0	1	36
Norway	1	0	0	1	36
Philippines	1	0	0	1	36
Thailand	1	0	0	1	36
Uzbekistan	1	0	0	1	36
Netherlands	0	3	0	3	15
Spain	0	2	1	3	15
Georgia	0	2	0	2	22
South Africa	0	2	0	2	22
Mongolia	0	1	2	3	15
Belgium	0	1	1	2	22
Czech Republic	0	1	1	2	22
Indonesia	0	1	1	2	22
Bulgaria	0	1	0	1	36
Colombia	0	1	0	1	36
Denmark	0	1	0	1	36
India	0	1	0	1	36
Jordan	0	1	0	1	36
North Macedonia	0	1	0	1	36
Romania	0	1	0	1	36
Turkmenistan	0	1	0	1	36
Kazakhstan	0	0	3	3	15
Ukraine	0	0	3	3	15
Egypt	0	0	2	2	22
Mexico	0	0	2	2	22
Turkey	0	0	2	2	22
Côte d'Ivoire	0	0	1	1	36
Cuba	0	0	1	1	36
Israel	0	0	1	1	36
Kuwait	0	0	1	1	36
New Zealand	0	0	1	1	36

BUT TOTAL_BY_RANK difference from GREAT Britain

please correct where i was wrong

MYCODE_OUTPUT

Obs	Team	Gold	Silver	Bronze	Total	total_by_ranks
1	Japan	10	3	5	18	3
2	United States of America	9	8	8	25	1
3	People's Republic of China	9	5	7	21	2
4	ROC	7	7	4	18	3
5	Great Britain	4	5	4	13	4
6	Republic of Korea	3	2	5	10	6
7	Australia	3	1	5	9	7
8	Canada	2	3	3	8	8
9	France	2	2	3	7	9
10	Germany	2	0	3	5	11
11	Kosovo	2	0	0	2	13
12	Italy	1	5	6	12	5
13	Chinese Taipei	1	2	3	6	10
14	Brazil	1	2	2	5	11
15	Switzerland	1	2	2	5	11
16	Slovenia	1	1	1	3	12
17	Serbia	1	1	1	3	12
18	Hungary	1	1	0	2	13
19	Tunisia	1	1	0	2	13
20	Austria	1	0	1	2	13
21	Croatia	1	0	1	2	13
22	Estonia	1	0	1	2	13
23	Bermuda	1	0	0	1	14
24	Ecuador	1	0	0	1	14
25	Hong Kong, China	1	0	0	1	14
26	Islamic Republic of Iran	1	0	0	1	14
27	Norway	1	0	0	1	14
28	Philippines	1	0	0	1	14
29	Thailand	1	0	0	1	14
30	Uzbekistan	1	0	0	1	14
31	Netherlands	0	3	0	3	12
32	Spain	0	2	1	3	12
33	Georgia	0	2	0	2	13
34	South Africa	0	2	0	2	13
35	Mongolia	0	1	2	3	12
36	Belgium	0	1	1	2	13
37	Czech Republic	0	1	1	2	13
38	Indonesia	0	1	1	2	13
39	Bulgaria	0	1	0	1	14
40	Colombia	0	1	0	1	14
41	Denmark	0	1	0	1	14
42	India	0	1	0	1	14
43	Jordan	0	1	0	1	14
44	North Macedonia	0	1	0	1	14
45	Romania	0	1	0	1	14
46	Turkmenistan	0	1	0	1	14
47	Kazakhstan	0	0	3	3	12
48	Ukraine	0	0	3	3	12
49	Egypt	0	0	2	2	13
50	Mexico	0	0	2	2	13
51	Turkey	0	0	2	2	13
52	Côte d'Ivoire	0	0	1	1	14
53	Cuba	0	0	1	1	14
54	Israel	0	0	1	1	14
55	Kuwait	0	0	1	1	14
56	New Zealand	0	0	1	1	14

Re: Rank by total

BrahmanandaRao — Tue, 27 Jul 2021 15:30:02 GMT

KurtBremser

THANK YOU VERY MUCH FOR YOUR SUPPORT

Re: Rank by total

ballardw — Tue, 27 Jul 2021 15:31:13 GMT

See what happens if you try different values for the TIES=option in Proc Rank.

From the documentation:

DENSE

computes scores and ranks by treating tied values as a single-order statistic. For the default method, ranks are consecutive integers that begin with the number one and end with the number of unique, nonmissing values of the variable that is being ranked. Tied values are assigned the same rank.