Proc Transpose Confusion

[pramenon1]

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

[PeterClemmensen · Accepted solution]

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]

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]

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]

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;