topic Re: How can I transpose a table like transposing a matrix? in SAS Programming

How can I transpose a table like transposing a matrix?

aalluru — Tue, 31 May 2022 14:21:35 GMT

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

Re: How can I transpose a table like transposing a matrix?

PaigeMiller — Tue, 31 May 2022 14:33:31 GMT

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?)

Re: How can I transpose a table like transposing a matrix?

aalluru — Tue, 31 May 2022 14:41:14 GMT

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

Re: How can I transpose a table like transposing a matrix?

PaigeMiller — Tue, 31 May 2022 14:46:43 GMT

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?

Re: How can I transpose a table like transposing a matrix?

aalluru — Tue, 31 May 2022 14:48:07 GMT

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

Re: How can I transpose a table like transposing a matrix?

PaigeMiller — Tue, 31 May 2022 14:51:45 GMT

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!

Re: How can I transpose a table like transposing a matrix?

aalluru — Tue, 31 May 2022 14:53:48 GMT

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.

Re: How can I transpose a table like transposing a matrix?

PaigeMiller — Tue, 31 May 2022 17:01:27 GMT

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.

Re: How can I transpose a table like transposing a matrix?

aalluru — Tue, 31 May 2022 15:07:22 GMT

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

Re: How can I transpose a table like transposing a matrix?

Tom — Tue, 31 May 2022 15:07:25 GMT

@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;

Re: How can I transpose a table like transposing a matrix?

Ksharp — Wed, 01 Jun 2022 04:34:21 GMT

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;

Re: How can I transpose a table like transposing a matrix?

Kurt_Bremser — Wed, 01 Jun 2022 06:54:44 GMT

@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.