Creating a symmetrical matrix with character variable to show diagonal and off diagonal cells

[dmarks]

The matrix output I desire should look like this ideally :

Disease A     B      C     D........L

A           100  56    29  14      63

B           56    100  36    18

C           20    36  100

D           14    18

.

.

L            63

[ADouglas]

Type 12 values of 9 in the array statement.

The 9 functions as a placeholder for each category.

[dmarks]

Thank-you for your reply ADouglas.

I have typed 14 x the value 9 into the code :

array x{99999999999999} $ 40 _temporary_;

but still no matrix appears. My output remains in Table form, as if each ID now had only 1 Disease obervation :

SAS Output

Obs	ID	Disease	COUNT	PERCENT
1	ID_240	GRP_12	36	0.19177
2	ID_241	GRP_10	25	0.13318
3	ID_242	GRP_1	16	0.08523
4	ID_243	GRP_3	81	0.43149
5	ID_244	GRP_5	4	0.02131
6	ID_245	GRP_6	36	0.19177
7	PART_150168	GRP_12	9	0.04794

Is my code correct for the TRANSPOSE step ?

proc transpose data=temp1 out=want(drop=_:);

by IdParticipant;

id Disease;

var count;

run;

[Ksharp]

Just change the variable name .
Why not use @Astounding 's code ? I think he have a better code.



data have;
input id       Disease $;
cards;
123            A
123            B
123            C
456            A
456            B
456            C
456            D
789            C
789            D
901            B
901            C
901            D
;
run;

data temp;
array x{9999} $ 40 _temporary_;
do i=1 by 1 until(last.id);
  set have;
  by id;
 x{i}=Disease;
end;

do m=1 to i;
 do n=m to i;
   v1=x{m};
   v2=x{n};
   output;
   if n ne m then do;
     v2=x{m};
     v1=x{n};
     output;
   end;
 end;
end;
keep id v1 v2;
run;

proc freq data=temp noprint;
 table v1*v2/out=temp1 list nopercent nocum;
run;

proc transpose data=temp1 out=want(drop=_:);
by v1;
id v2;
var count;
run;

[dmarks]

Thanks for your reply Ksharp.

I have tried to use @Astounding's code to create  a matrix for my 2 variables ID and Disease :

proc sql;
create table matrix
as select a.Disease as pa_1, b.Disease as pa_2
from sasuser.disease
where a.IdParticipant = b.IdParticipant;
quit;

proc freq data=matrix;
tables pa_1*pa_2 / norow nocol nopercent;
run;

However i receive the following error message :

185 where a.ID = b.ID;
ERROR: Unresolved reference to table/correlation name a.
ERROR: Unresolved reference to table/correlation name b.
ERROR: Unresolved reference to table/correlation name a.
ERROR: Unresolved reference to table/correlation name b.
186 quit;

Is my code correct ?

[Ksharp]

You don't have variable IdParticipant  ,only have ID, So sql should like :

proc sql;
create table matrix
as select a.Disease as pa_1, b.Disease as pa_2
from sasuser.disease
where a.Id= b.Id;
quit;

[dmarks]

Ksharp, thank-you so much again for replying.

I have replaced "IdParticipant" with "ID" for the variable, but I still get an error message :

409 proc sql;
410 create table matrix
411 as select a.Disease as pa_1, b.Disease as pa_2
412 from sasuser.disease
413 where a.ID= b.ID;
ERROR: Unresolved reference to table/correlation name a.
ERROR: Unresolved reference to table/correlation name b.
ERROR: Unresolved reference to table/correlation name a.
ERROR: Unresolved reference to table/correlation name b.
414 quit;

[Ksharp]

Opps. You need define a table reference for table.

proc sql;
create table matrix
as select a.Disease as pa_1, b.Disease as pa_2
from sasuser.disease as a , sasuser.disease as b
where a.Id= b.Id;
quit;

[dmarks]

Using both Ksharp and @Astounding codes, I manage to obtain a 14x14 matrix at the end for Disease (see attached file).

This is the desired variable, but it is a diagonal matrix which does not cross-tabulate of how many subjects with DIS_2 ALSO had DIS_6 or DIS_8 etc. There are zeros for all combinations instead.

data temp;
array x{9999} $ 40 _temporary_;
do i=1 by 1 until(last.IdParticipant);
set sasuser.disease;
by IdParticipant;
x{i}=Group;
end;
do m=1 to i;
do n=m to i;
v1=x{m};
v2=x{n};
output;
if n ne m then do;
v2=x{m};
v1=x{n};
output;
end;
end;
end;
keep IdParticipant Group;
run;

proc freq data=temp;
table IdParticipant*Group/out=temp1 list nopercent nocum;
run;

proc transpose data=temp1 out=want(drop=_:);
by IdParticipant;
id Group;
var count;
run;

proc sql;
create table matrix
as select a.Group as pa_1, b.Group as pa_2
from temp1 as a , temp1 as b
where a.IdParticipant= b.IdParticipant;
quit;

proc freq data=matrix;
tables pa_1 * pa_2 / norow nocol nopercent;
run;

[Ksharp]

Better post your original data to debug the code.

[ADouglas]

Yes, send data so the code can be generalized to your situation. The code works for me.

[Astounding]

This SQL code accurately reflects my original idea.  Notice two things:

• It works on your original data, not the result that comes of a DATA step
• It would display better if you were to change variable names:  DIS_01 instead of DIS_1, DIS_02 instead of DIS_2, etc.

[dmarks]

Dear all,

The PROC SQL method worked for me, I just had to correct the FROM statement in the code for my original data.

Many thanks for your help, much appreciated!

D

[dmarks]

ADouglas,

The code only works if I leave 4 x values of 9 in the array step.

But the output is a 4 colulm table instead of a 14x14 matrix...

data temp;

array x{9999} $ 40 _temporary_;

do i=1 by 1 until(last.ID);

set sasuser.disease;

by ID;

x{i}=Disease;

end;

Other wise I receive an error message for that line.

(The Disease variable has 14 different responses possible, not just 4)

Am I perhaps missing a line of code to display the matrix ?