How can I transpose a table like transposing a matrix?

[aalluru]

I have a table that looks something like this (there are actually way more rows and columns than this):

month     a     b     c

202101   1     2      1

202102    4    3      2

I want to make it look like this:

var       202101        202102

a          1                  4

b           2                 3

c           1                  2    

[PaigeMiller]

Is your goal by doing this to have a report that can be printed or included in some sort of document?

Or is your goal to create a SAS data set for further analysis? (If so, what analysis?)

[aalluru]

I'm trying to have that as a final dataset but I want it to be on SAS so I can directly email that table to someone

[PaigeMiller]

What is the purpose of having a data set in this format? What analyses will be done with it after you e-mail it to someone?

[aalluru]

I'm not going to do any further analysis. I just want to to email it directly without having to export into excel

[PaigeMiller]

Yes, I want to know what analysis the person receiving the data is going to do.

However, my standard advice is this: don't convert/transpose the data to that format. It is a less usable format, requiring more work to program from. It doesn't get any simpler than that!

[aalluru]

Oh I see. The person receiving the email won't do any analysis. It's just meant to be a monthly report sort of thing. Just wanted to do this because if we leave it as it is, there'll be around 40 columns which isn't really that presentable.

[PaigeMiller]

So the goal is really to create a report from this data! See Maxim 19, Long Beats Wide! 

data have;
input month yymmn6.    a     b     c;
cards;
202101   1     2      1
202102   4     3      2
;

data long;
    set have;
    length variable $ 32;
    array vars a--c;
    do i=1 to dim(vars);
        variable=vname(vars(i));
        value=vars(i);
        output;
    end;
    drop a--c i;
run;

proc report data=long;
    columns variable month,value;
    define variable/group;
    define month/across format=yymmn6. order=internal;
    define value/sum;
run;

But to back up a step, and apply Maxim 19, you should not be creating this data set in the format given; you should create it initially as a long data set and then all of these gymnastics to get it into a usable form are unnecessary.

[aalluru]

some of the columns in the table I have also have character values so this doesn't seem to work in that case. Also, the column names aren't a, b, c, etc. They don't really follow a pattern like that

[Tom]

---

@aalluru wrote:
I'm trying to have that as a final dataset but I want it to be on SAS so I can directly email that table to someone

---

But your requested output is not a dataset.  It has MONTH values in the NAMES of the variables instead of a actual data.  They will not be able to use that format in any analysis.

Are you trying to make a report to send to someone?

It helps if you have an actual unique key for the orignal dataset.  If you don't have one then make one. You can then transpose into a workable format.

data step1;
  row+1;
  set have;
run;

proc transpose data=have out=tall name=var;
  by row month;
  var a b c ;
run;

Now you can either make a report.

proc report data=tall;
  column var col1,month ;
  define var / group 'VAR';
  define month / across ' ';
  define col1 / sum ' ';
run;

Or you can sort and transpose again.  But in that case you will not get "202101" as the column headers unless you have accidentally turned on VALIDVARNAME=ANY.

proc sort data=tall;
  by var row;
run;

proc transpose data=tall out=want(drop=_name_);
  by var ;
  id month;
  var col1;
run;

proc print;
run;

[Ksharp]

Just like SAS/IML did ? Or you want IML code ?

data have;
input month yymmn6.    a     b     c;
cards;
202101   1     2      1
202102   4     3      2
;
proc transpose data=have out=tall name=var;
  var  a b c ;
  id month;
  format month yymmn6.;
run;

[Kurt_Bremser]

---

@aalluru wrote:

I have a table that looks something like this (there are actually way more rows and columns than this):

month     a     b     c

202101   1     2      1

202102    4    3      2

I want to make it look like this:

var       202101        202102

a          1                  4

b           2                 3

c           1                  2    

---

Make your dataset "longer", then use PROC REPORT:

proc transpose data=have out=long;
by month;
var a--c; /* variable list by position, only the first and last variable needs to be named */
run;

proc report data=long;
column _name_ col1,month;
define _name_ / "var" group;
define col1 / "" analysis;
define month / "" across;
run;

Wrap the report step into a proper ODS destination for later use as an email attachment.

Code is untested, posted from my tablet.