topic Re: Reshaping data in SAS/IML Software and Matrix Computations

Reshaping data

coder1 — Wed, 23 May 2018 20:23:49 GMT

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?

Re: Reshaping data

SuryaKiran — Wed, 23 May 2018 21:12:12 GMT

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

Re: Reshaping data

coder1 — Wed, 23 May 2018 21:39:00 GMT

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

Re: Reshaping data

Ksharp — Thu, 24 May 2018 12:47:27 GMT

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;

Re: Reshaping data

coder1 — Thu, 24 May 2018 14:07:23 GMT

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;

Re: Reshaping data

Rick_SAS — Thu, 24 May 2018 14:42:38 GMT

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;