topic Re: converting matrix to two columns in SAS Procedures

converting matrix to two columns

brophymj — Fri, 15 Apr 2016 15:20:52 GMT

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

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

Re: converting matrix to two columns

ballardw — Fri, 15 Apr 2016 15:29:28 GMT

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;

Re: converting matrix to two columns

data_null__ — Sat, 16 Apr 2016 12:43:14 GMT

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;

Re: converting matrix to two columns

PGStats — Fri, 15 Apr 2016 15:45:03 GMT

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;

Re: converting matrix to two columns

brophymj — Fri, 15 Apr 2016 15:48:40 GMT

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?

Re: converting matrix to two columns

Ksharp — Sat, 16 Apr 2016 04:34:05 GMT

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;
read all var{RowName};
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;