BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
coder1
Calcite | Level 5

I want to reshape a matrix created in IML and convert it to a sas dataset while retaining the column and row names. For example,  suppose I have an N by N matrix with row and column names.  The end product I want is a sas dataset with 3 variables.  The first variable is created by stacking the columns of the original matrix (implying the dataset has N^2 observations).  The second variable is a character variable with the appropriate column name from the original matrix.  The third column is a character variable with the appropriate row name from the original matrix.  Is this possible?  

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

Yes, you can "trust Varnames to be in the right order."

You can also simplify your program by writing the rownames directly instead of writing two data sets and merging the data:

 

create cmat from Z [colname=varNames rowname=varNames];
append from Z[rowname=varNames];
close cmat;

View solution in original post

5 REPLIES 5
SuryaKiran
Meteorite | Level 14

Please provide a sample data you have and the result your looking for.

Thanks,
Suryakiran
coder1
Calcite | Level 5

The matrix as created in IML is:

 

1 2

3 4

 

The column names are c1 and c2;

The row names are r1 and r2

 

end result is a sas dataset that looks like this:

 

1 r1 c1

3 r2 c1

2 r1 c2

4 r2 c2

Ksharp
Super User

You really should post it at IML forum ,since it is about IML question.

 

proc iml;
x={1 2,
   3 4};
row_name='r1':'r2';
col_name='c1':'c2';

row=row_name[row(x)];
col=col_name[col(x)];
value=colvec(x);

create want var{value row col};
append;
close;
quit;

proc print data=want noobs;run;

coder1
Calcite | Level 5

Thanks Ksharp,

 

If you explicitly define the row and column names as character vectors, then as your code shows below, this is somewhat trivial.  I have a very large matrix with thousands of columns, and am pulling the variable names using varnames=contents().  Explicity defining them as character vectors would be impractical.  I created some code that I think does what I need it to (below).  I guess my only question is, can I always trust Varnames to be in the right order to specify the colnames and rownames of the correlation matrix as I do below? 

 

proc iml;
use cdat1;
varNames = contents();
read all var _NUM_ into X[colname=varNames]; /* numerical data */
close cdat1;

Z=corr(X,'Pearson','Pairwise');

 

mattrib Z rowname=varnames colname=varnames
spearman rowname=varnames colname=varnames;

 

nn=varnames`;

create cmat from Z [colname=varNames];
append from Z;
close cmat;

 

create nmat from nn;
append from nn;
close nmat;

quit;
/*------------------------*/
data corrdat;
merge nmat cmat;
run;

data corrdat;
set corrdat;
perm1=col1;
drop col1;

proc sort data=corrdat;
by perm1;

proc transpose data=corrdat out=corrdat1;
by perm1;
run;

 

Rick_SAS
SAS Super FREQ

Yes, you can "trust Varnames to be in the right order."

You can also simplify your program by writing the rownames directly instead of writing two data sets and merging the data:

 

create cmat from Z [colname=varNames rowname=varNames];
append from Z[rowname=varNames];
close cmat;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 5 replies
  • 979 views
  • 2 likes
  • 4 in conversation