BookmarkSubscribeRSS Feed
BrahmanandaRao
Lapis Lazuli | Level 10
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

20 REPLIES 20
andreas_lds
Jade | Level 19

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
Lapis Lazuli | Level 10

Anandkvn_0-1627370542948.png

 

Kurt_Bremser
Super User
 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
Lapis Lazuli | Level 10
Spoiler
proc import datafile ='/home/u35263349/My_Folder/Olympics.xlsx'
out=ranks_total_by_country
dbms=xlsx;
run;
BrahmanandaRao
Lapis Lazuli | Level 10
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
Super User

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
Lapis Lazuli | Level 10

please give solution 

Kurt_Bremser
Super User

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
Lapis Lazuli | Level 10
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
Super User

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
Lapis Lazuli | Level 10

but in my proc summar did not sum three variables why 

Kurt_Bremser
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 20 replies
  • 2556 views
  • 2 likes
  • 4 in conversation