BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
ballardw
Super User

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;

brophymj
Quartz | Level 8

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?

 

 

data_null__
Jade | Level 19

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

PGStats
Opal | Level 21

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
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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