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?
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;
Please provide a sample data you have and the result your looking for.
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
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.