Converting long table into sas table in SAS

Question

I am trying to convert a long table into wide table in SAS. I have the following data

Team ID Player  Score
1        Andy   12
2       Andy    32
1       Andy    2
3       Andy    0
1       Andy    43
3         Bin   33
1        Bin    23
3        Bin    34
3         Bin   3
1         Bin   12
2        Ray    34
3        Ray    52
2        Ray    11

Now I want to add score of each player teamwise like

Team ID Player  Score
1       Andy    46
1       Ray     34
1       Bin     33
2       Andy    43
2        Ray    52
3         Bin   72
3         Ray   11

Now I want to tanspose the players so that I get one row for each team like this.

Team ID Andy    Ray Bin
1        46     34  33
2        43     52  .
3          .    11  72

I tried with proc transpose and proc means but couldn't find a suitable solution. Will appreciate your help for a sas code.

Regards

Hi you might want to fix that formatting to aid readability. Also, could you share what code you've tried so far? — Alex Lynham
Can you explain how Andy gets a score of 46 in the second table? — Robert Penridge
sorry for not posting correct values in table 2 @Robert Penridge my apologies for the incorrect value for Andy, It should be sum of all values of Andy in each team seperately. Anyhow as I had a very very large table what I did in the end is used prom means to sum scores using class statement and then transposing it. Thanks for the ideas. — Philanthrope

user3714330 user3714330 · Accepted Answer · 2015-06-30T14:35:46

This might work. First sort your data by "Team":

Proc SORT Data=Have Out=Want1;
by Team;
Run;

Then Transpose your data as your variable will be sorted "By Team" (vertical) with "Id Player" (horizontal):

PROC TRANSPOSE Data=Want1 Out=Want2 let;
Id Player;
By Team;
Var Score;
Run;

Converting long table into sas table in SAS

1 Answers