## converting matrix to two columns

Solved
Super Contributor
Posts: 261

# converting matrix to two columns

Is there a straighforward way to convert

 RowName Prm1 Prm2 Prm3 Prm4 Prm5 Prm6 Prm1 1 -0.1487 -0.0531 -0.11 -0.2161 -0.1575 Prm2 -0.1487 1 0.0079 0.0163 0.0321 0.0234 Prm3 -0.0531 0.0079 1 0.0058 0.0115 0.0084 Prm4 -0.11 0.0163 0.0058 1 0.0238 0.0173 Prm5 -0.2161 0.0321 0.0115 0.0238 1 0.034 Prm6 -0.1575 0.0234 0.0084 0.0173 0.034 1

to

 Field1 Field2 Field3 Prm1 Prm1 1 Prm1 Prm2 -0.1487 Prm1 Prm3 -0.0531 Prm1 Prm4 -0.11 Prm1 Prm5 -0.2161 Prm1 Prm6 -0.1575 Prm2 Prm2 1 Prm2 Prm3 0.0079 Prm2 Prm4 0.0163 Prm2 Prm5 0.0321 Prm2 Prm6 0.0234 Prm3 Prm3 1 Prm3 Prm4 0.0058 Prm3 Prm5 0.0115 Prm3 Prm6 0.0084 Prm4 Prm4 1 Prm4 Prm5 0.0238 Prm4 Prm6 0.0173 Prm5 Prm5 1 Prm5 Prm6 0.034 Prm6 Prm6 1

Accepted Solutions
Solution
‎04-15-2016 11:50 AM
Posts: 5,526

## Re: converting matrix to two columns

Another way:

``````data test;
input RowName \$	Prm1	Prm2	Prm3	Prm4	Prm5	Prm6;
datalines;
Prm1	1	-0.1487	-0.0531	-0.11	-0.2161	-0.1575
Prm2	-0.1487	1	0.0079	0.0163	0.0321	0.0234
Prm3	-0.0531	0.0079	1	0.0058	0.0115	0.0084
Prm4	-0.11	0.0163	0.0058	1	0.0238	0.0173
Prm5	-0.2161	0.0321	0.0115	0.0238	1	0.034
Prm6	-0.1575	0.0234	0.0084	0.0173	0.034	1
;

proc transpose data=test out=list(where=(Rowname<=_name_));
by rowname;
var prm: ;
run;
``````
PG

All Replies
Super User
Posts: 13,523

## Re: converting matrix to two columns

One way:

``````
data want (keep=field1 field2 field3);
set have;
length field1 field2 \$ 32.;
array p Prm1-Prm6;
do i=1 to dim(p);
Field1 = RowName;
Field2 = vname(p[i]);
Field3 = p[i];
output;
end;
run;

``````
Super Contributor
Posts: 261

## Re: converting matrix to two columns

Thanks ballardw

This code creates 36 rows whereas I want to exclude multiples of the same combination so..

prm1 prm2

prm1 prm3

prm1 prm4

prm1 prm5

prm1 prm6

prm2 prm2 (i.e. exclude prm2 prm1 as is already included above)

prm2 prm3

...

...

Is there a way to adjust your code to allow for this?

Posts: 3,852

## Re: converting matrix to two columns

[ Edited ]

Updated to answer the implied upper triangular requirement.

``````data prm;
infile cards expandtabs;
input RowName \$	Prm1	Prm2	Prm3	Prm4	Prm5	Prm6;
cards;
Prm1	1	-0.1487	-0.0531	-0.11	-0.2161	-0.1575
Prm2	-0.1487	1	0.0079	0.0163	0.0321	0.0234
Prm3	-0.0531	0.0079	1	0.0058	0.0115	0.0084
Prm4	-0.11	0.0163	0.0058	1	0.0238	0.0173
Prm5	-0.2161	0.0321	0.0115	0.0238	1	0.034
Prm6	-0.1575	0.0234	0.0084	0.0173	0.034	1
;;;;
run;
proc transpose data=prm name=ColName out=prm2(where=(rowname LE colname));
by rowname notsorted;
run;
``````

Solution
‎04-15-2016 11:50 AM
Posts: 5,526

## Re: converting matrix to two columns

Another way:

``````data test;
input RowName \$	Prm1	Prm2	Prm3	Prm4	Prm5	Prm6;
datalines;
Prm1	1	-0.1487	-0.0531	-0.11	-0.2161	-0.1575
Prm2	-0.1487	1	0.0079	0.0163	0.0321	0.0234
Prm3	-0.0531	0.0079	1	0.0058	0.0115	0.0084
Prm4	-0.11	0.0163	0.0058	1	0.0238	0.0173
Prm5	-0.2161	0.0321	0.0115	0.0238	1	0.034
Prm6	-0.1575	0.0234	0.0084	0.0173	0.034	1
;

proc transpose data=test out=list(where=(Rowname<=_name_));
by rowname;
var prm: ;
run;
``````
PG
Super User
Posts: 10,770

## Re: converting matrix to two columns

It is IML thing.
```data test;
infile datalines expandtabs truncover;
input RowName \$	Prm1	Prm2	Prm3	Prm4	Prm5	Prm6;
datalines;
Prm1	1	-0.1487	-0.0531	-0.11	-0.2161	-0.1575
Prm2	-0.1487	1	0.0079	0.0163	0.0321	0.0234
Prm3	-0.0531	0.0079	1	0.0058	0.0115	0.0084
Prm4	-0.11	0.0163	0.0058	1	0.0238	0.0173
Prm5	-0.2161	0.0321	0.0115	0.0238	1	0.034
Prm6	-0.1575	0.0234	0.0084	0.0173	0.034	1
;
run;
proc iml;
use test;
read all var _num_ into x;
close;

r=repeat(RowName,1,ncol(x));
c=repeat(t(RowName),nrow(x),1);
idx=loc(row(x)<=col(x));
field1=r[idx];
field2=c[idx];
field3=x[idx];

create want var{field1 field2 field3};
append;
close;
quit;

```
🔒 This topic is solved and locked.