Help using Base SAS procedures

converting matrix to two columns

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

converting matrix to two columns

Is there a straighforward way to convert

 

RowNamePrm1Prm2Prm3Prm4Prm5Prm6
Prm11-0.1487-0.0531-0.11-0.2161-0.1575
Prm2-0.148710.00790.01630.03210.0234
Prm3-0.05310.007910.00580.01150.0084
Prm4-0.110.01630.005810.02380.0173
Prm5-0.21610.03210.01150.023810.034
Prm6-0.15750.02340.00840.01730.0341

 

 

to

 

Field1Field2Field3
Prm1Prm11
Prm1Prm2-0.1487
Prm1Prm3-0.0531
Prm1Prm4-0.11
Prm1Prm5-0.2161
Prm1Prm6-0.1575
Prm2Prm21
Prm2Prm30.0079
Prm2Prm40.0163
Prm2Prm50.0321
Prm2Prm60.0234
Prm3Prm31
Prm3Prm40.0058
Prm3Prm50.0115
Prm3Prm60.0084
Prm4Prm41
Prm4Prm50.0238
Prm4Prm60.0173
Prm5Prm51
Prm5Prm60.034
Prm6Prm61

Accepted Solutions
Solution
‎04-15-2016 11:50 AM
Respected Advisor
Posts: 4,920

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

View solution in original post


All Replies
Super User
Posts: 11,343

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: 259

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?

 

 

Respected Advisor
Posts: 3,799

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;

Capture.PNG

Solution
‎04-15-2016 11:50 AM
Respected Advisor
Posts: 4,920

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,023

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;
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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 339 views
  • 2 likes
  • 5 in conversation