Rank by total

[BrahmanandaRao]

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

[andreas_lds]

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.

[BrahmanandaRao]

[Kurt_Bremser]

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

[Kurt_Bremser]

 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.

[BrahmanandaRao]

Spoiler

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

[Kurt_Bremser]

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

[BrahmanandaRao]

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

[Kurt_Bremser]

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.

[BrahmanandaRao]

please give solution 

[Kurt_Bremser]

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.

[BrahmanandaRao]

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 

[Kurt_Bremser]

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.

[BrahmanandaRao]

but in my proc summar did not sum three variables why 

[Kurt_Bremser]

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.