topic Re: Proc Transpose Confusion in SAS Visual Analytics

Proc Transpose Confusion

pramenon1 — Thu, 28 Oct 2021 07:11:11 GMT

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

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

Re: Proc Transpose Confusion

PeterClemmensen — Thu, 28 Oct 2021 07:38:17 GMT

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;

Re: Proc Transpose Confusion

PeterClemmensen — Thu, 28 Oct 2021 07:41:03 GMT

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;

Re: Proc Transpose Confusion

Madelyn_SAS — Thu, 28 Oct 2021 13:24:07 GMT

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

Re: Proc Transpose Confusion

Tom — Thu, 28 Oct 2021 13:49:41 GMT

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;