Learning SAS? Welcome to the exclusive online community for all SAS learners.

Transpose of Matrix Data

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Transpose of Matrix Data

[ Edited ]

Hello Everyone,

 

Here, I have attached an excel file Matrix_New which contains 3 sheets first two are for data (case_1 and case_2) while last sheet is to display what is my objective. 

 

Please anyone try to solve it. I fail to solve it, I used proc transpose but I am not getting what I want.

 

Regards,
AG_Stats

Accepted Solutions
Solution
‎04-19-2016 12:44 PM
Contributor
Posts: 65

Re: Transpose of Matrix Data

[ Edited ]

Thanks for considering my problem. Sorry, I have not elaborately explained what I need.

But now I get the required codes (applicable if data starts from cell (1,1) i.e. first column and first row, and I don't know if my data starts from irregular cells)

 

options nocenter compress=yes;

FILENAME MYFILE '/folders/myfolders/Matrix.xlsx';

 

PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH1;
GETNAMES=NO;
SHEET="Sheet1";
RUN; /* proc delete; run; */
PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH2;
GETNAMES=YES;
SHEET="Sheet2";
RUN; /* proc delete; run; */

/* For Sheet-1 */
proc print data=sh1 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh1 out=sh1_transposed(rename=(_name_=Attributes col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_LABEL_);
var _ALL_; *var _Numeric_;
run;
proc print data=sh1_transposed noobs;
title 'The Output Data Set';
run;


/* For Sheet-2 */
proc print data=sh2 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh2 out=sh2_transposed(rename=(_Name_=Variable col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_label_);
*var _ALL_; var _Numeric_;
run;
proc print data=sh2_transposed noobs;
title 'The Output Data Set';
run;

 

And sorry, I have made little changes to my data file. 

Regards,
AG_Stats

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,744

Re: Transpose of Matrix Data

Hi:
What is the code you have tried? Please show your transpose code. It is puzzling to me why you can't get transpose working. It seems to me you have 2 questions: 1) how to get the desired output and 2) how to import from excel whether the data starts in the first row or the second.

At any rate a bit more information is needed.
1) What code have you tried already for both your Case 1 and Case 2
2) What does the input file look like -- does it come from Excel? Why would the data change so that you don't know what row the data starts in?
3) What is the desired output? A report for both cases? A dataset for Case 1? A dataset for Case 2?
4) If you want a report, what is your desired destination (listing, HTML, RTF, PDF, etc)?
5) Your Case 3 does not have any titles or column headers, particularly for what you show of the Case 2 desired results?
6) Speaking of column headers, there are no column names or variable names in Case 2 data -- so is this representative of how you are going to get the data? SAS will need to have variable names to work with the data, even if they are just var1, and var2 -- do you have any specific requirements for the variable names used?
7) Do you need to read Case 1 data from Excel and then read Case 2 data from Excel and then write Case 3 into the same workbook??? If so, this will change the possible techniques that someone will use.

Without more information it is hard to do more than make general suggestions. If reading the data is an issue for you, as implied in your notes, then when you post the code you've tried, you'll need to post your code for reading the data, as well.

cynthia
Solution
‎04-19-2016 12:44 PM
Contributor
Posts: 65

Re: Transpose of Matrix Data

[ Edited ]

Thanks for considering my problem. Sorry, I have not elaborately explained what I need.

But now I get the required codes (applicable if data starts from cell (1,1) i.e. first column and first row, and I don't know if my data starts from irregular cells)

 

options nocenter compress=yes;

FILENAME MYFILE '/folders/myfolders/Matrix.xlsx';

 

PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH1;
GETNAMES=NO;
SHEET="Sheet1";
RUN; /* proc delete; run; */
PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH2;
GETNAMES=YES;
SHEET="Sheet2";
RUN; /* proc delete; run; */

/* For Sheet-1 */
proc print data=sh1 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh1 out=sh1_transposed(rename=(_name_=Attributes col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_LABEL_);
var _ALL_; *var _Numeric_;
run;
proc print data=sh1_transposed noobs;
title 'The Output Data Set';
run;


/* For Sheet-2 */
proc print data=sh2 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh2 out=sh2_transposed(rename=(_Name_=Variable col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_label_);
*var _ALL_; var _Numeric_;
run;
proc print data=sh2_transposed noobs;
title 'The Output Data Set';
run;

 

And sorry, I have made little changes to my data file. 

Regards,
AG_Stats
Contributor
Posts: 39

Re: Transpose of Matrix Data

I added variable/column names and also a row variable to your data.  I assumed that the data values, except for the row variable, are all character data.  The macro will take a matrix and its dimensions and transpose it into a new one.  Look below the macro definition to see how I've input your data and the output that I got.  This may not be exactly what you are looking for, but hope it helps.

%macro transpose_matrix(matrix, nRows, nCols, matrix2);

  data &matrix2 (keep = row c1-c&nRows);

       format row 3.;

       array c{1,&nRows} $ 1;

       c1 = ' ';

       do i = 1 to &nCols;

          row = i;

          output;

       end;

  run;

  %local i j;

  %do i = 1 %to &nRows; * Number of New Columns;

      %do j = 1 %to &nCols; * Number of New Rows;

          %*put (i,j) = (&i,&j);

          proc sql;

          update &matrix2

          set c&i = (select c&j from &matrix

                     where row = &i)

          where row = &j;

      %end;

  %end;

%mend transpose_matrix;

 

data temp;

input c1 $ c2 $ c3 $ c4 $;

datalines;

A B C D

1 3 5 6

2 5 5 8

;

data case1;

  row = _N_;

  set temp;

run;

 

data temp;

input c1 $ c2 $;

datalines;

1 5

2 8

3 5

4 2

;

data case2;

  row = _N_;

  set temp;

run;

 

%transpose_matrix(case1, 3, 4, case1_trans);

%transpose_matrix(case2, 4, 2, case2_trans);

 

proc sql;

select c1, c2, c3

from case1_trans

order by row;

/*

c1 c2 c3

----------

A  1  2

B  3  5

C  5  5

D  6  8

*/

proc sql;

select c1, c2, c3, c4

from case2_trans

order by row;

/*

c1 c2 c3 c4

--------------

1  2  3  4

5  8  5  2

*/

 

Contributor
Posts: 65

Re: Transpose of Matrix Data

Thank you as you have given me a solution and used concepts of Advanced SAS (macros) it may be helpful for me in future also.

 

But, at this time I don't want to change the parameters / values of the following macro statement:

%transpose_matrix(case1, 3, 4, case1_trans);

%transpose_matrix(case2, 4, 2, case2_trans);

 

Because I have to repeat my procedure 35-50 times and I have different No. of Rows each time.

 

The following codes are working well:

 

options nocenter compress=yes;

FILENAME MYFILE '/folders/myfolders/Matrix.xlsx';

PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH1;
GETNAMES=NO;
SHEET="Sheet1";
RUN; /* proc delete; run; */
PROC IMPORT DATAFILE=MYFILE
DBMS=XLSX
OUT=WORK.SH2;
GETNAMES=YES;
SHEET="Sheet2";
RUN; /* proc delete; run; */

 

/* For Sheet-1 */
proc print data=sh1 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh1 out=sh1_transposed(rename=(_name_=Attributes col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_LABEL_);
var _ALL_; *var _Numeric_;
run;
proc print data=sh1_transposed noobs;
title 'The Output Data Set';
run;


/* For Sheet-2 */
proc print data=sh2 noobs;
title 'The Input Data Set';
run;
proc transpose data=sh2 out=sh2_transposed(rename=(_Name_=Variable col1-col1000 = Member1-Member1000 /*Generalized to 1000 members*/) drop=_label_);
*var _ALL_; var _Numeric_;
run;
proc print data=sh2_transposed noobs;
title 'The Output Data Set';
run;

 

However, I have changed my data to a little extent. 

 

Again thanks for your Consideration.

 

Regards,
AG_Stats
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 640 views
  • 2 likes
  • 3 in conversation