SAS transpose columns to row and values to columns

Question

I have a summary table which I want to transpose, but I can't get my head around. The columns should be the rows, and the columns are the values.

Some explanation about the table. Each column represents a year. People can be in 3 groups: A, B or C. In 2016, everyone (100) is in group A. In 2017, 35 are in group A (5 + 20 + 10), 15 in B and 50 in C.

DATA have;
    INPUT year2016 $ year2017 $ year2018 $ count;
    DATALINES;
A A A 5
A A B 20
A A C 10
A B C 15
A C A 50
    ;
RUN;

I want to be able to make a nice graph of the evolution of the groups through the different periods. So I want to end up with a table where the columns are the rows (=period) and the columns are the values (= the 3 different groups). Please find an example of the table I want:

Image of table want

I have tried different approaches, but I can't get what I want.

Please show what you've tried, proc transpose should work fine. stats.idre.ucla.edu/sas/modules/… — Reeza
Actually, you may want something like this instead...stackoverflow.com/questions/54096714/… — Reeza

data _null_ data _null_ · Accepted Answer · 2019-01-08T18:32:08

Maybe more direct way but this is probably how I would do it.

DATA have;
    INPUT year2016 $ year2017 $ year2018 $ count;
    id + 1;
    DATALINES;
A A A 5
A A B 20
A A C 10
A B C 15
A C A 50
;
   RUN;
proc print;
proc transpose data=have out=want1 name=period;
   by id count notsorted;
   var year:;
   run;
proc print;
   run;
proc summary data=want1 nway completetypes;
   class period col1;
   freq count;
   output out=want2(drop=_type_);
   run;
proc print;
   run;
proc transpose data=want2 out=want(drop=_name_) prefix=Group_;
   by period;
   var _freq_;
   id col1;
   run;
proc print;
   run;

SAS transpose columns to row and values to columns

1 Answers