BookmarkSubscribeRSS Feed
☑ This topic is solved. 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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

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
  • 410 views
  • 0 likes
  • 4 in conversation