BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pramenon1
Obsidian | Level 7

Hi, my data currently looks like this:

 

Country----Gold-----Silver------Bronze

US---------10---------12--------------15

China-------15--------10-------------4

GB----------7---------4-------------12

 

 

I want it to look like this:

 

Country------Medal-------Count

US----------Gold------------X

US---------Silver------------Y

US----------Bronze---------Z

China

China

China

 

 

An so on and so forth. the reason for this is that I need to make 1 bar chart for the 3 different countries with each of the 3 medal counts.

 

I tried using proc transpose but it seems I am doing something wrong. here is my code:

 

PROC TRANSPOSE DATA=olympics OUT=medal;
ID Country_Name;
var gold silver bronze;

RUN;

 

Any help would be appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

Try this

 

data have;
input Country $ Gold Silver Bronze;
datalines;
US    10 12 15
China 15 10 4
GB    7  4  12
;

proc sort data = have;
   by Country;
run;

proc transpose data = have out = want(rename = (_NAME_ = Medal COL1 = Count));
   by Country;
run;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

Try this

 

data have;
input Country $ Gold Silver Bronze;
datalines;
US    10 12 15
China 15 10 4
GB    7  4  12
;

proc sort data = have;
   by Country;
run;

proc transpose data = have out = want(rename = (_NAME_ = Medal COL1 = Count));
   by Country;
run;
Madelyn_SAS
SAS Super FREQ

Note that a question about programming should be directed to the SAS Programming community. That will ensure that the right folks see the message. 

PeterClemmensen
Tourmaline | Level 20

Or

 

data have;
input Country $ Gold Silver Bronze;
datalines;
US    10 12 15
China 15 10 4
GB    7  4  12
;

data want(keep = Country Medal Count);
   set have;
   array m Gold -- Bronze;
   do over m;
      Medal = vname(m);
      Count = m;
      output;
   end;
run;
Tom
Super User Tom
Super User

The ID statement is for telling PROC TRANSPOSE which variable to use to name the new columns.

You want a BY statement.  If you data is not sorted by COUNTRY_NAME then add the NOTSORTED keyword to the BY statement.

PROC TRANSPOSE DATA=olympics name=Country
  OUT=medal(rename=(col1=Count)) 
;
  by Country_Name notsorted ;
  var gold silver bronze;
RUN;

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!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 684 views
  • 0 likes
  • 4 in conversation