turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Learn SAS
- /
- Analytics U
- /
- Transpose of Matrix Data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-14-2016 03:34 AM - edited 04-15-2016 05:36 AM

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

AG_Stats

Accepted Solutions

Solution

04-19-2016
12:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

04-16-2016 07:21 AM - edited 04-16-2016 07:22 AM

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

AG_Stats

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AG_Stats

04-15-2016 09:48 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

04-16-2016 07:21 AM - edited 04-16-2016 07:22 AM

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

AG_Stats

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AG_Stats

04-15-2016 12:19 PM

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,

%** transpose_matrix**(case2,

**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

*/

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Pamela_JSRCC

04-16-2016 07:30 AM

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,

%** transpose_matrix**(case2,

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

AG_Stats