n*n table with multiple variables

[km0927]

Hi. Thanks for your consideration in advance.

I'm trying to make tables with multiple variables. The Data are as follows

id fever vomiting redness swelling

1    1          0          1             1

2    1          1          0             0

3    0          1          1             1

4    1          0          1             0

5    0          0          1             1

i want to find their correlation in frequency. The 'want' table is as follows.

                    fever           vomiting          redness        swelling

fever              3                     1                   2                   1

vomiting        1                     2                   1                    0

redness         2                     1                   1                   3

swelling         1                     0                   3                   3

So, there are 2 patients who had vomiting and fever. In swelling and redness, there are 1 patient.

It takes a lot of time to do proc freq with every combination.

Is there any way to make that table simply?

[andreas_lds]

Please verify that the values in want match those in have. In the last row the value for vomiting seems to be wrong, same for redness/redness.

[km0927]

I'm really sorry for my mistake. I corrected it.

[Astounding]

Evidently this is a difficult task to describe. I would suggest a different result:

proc freq data=have;
tables fever * vomiting * redness * swelling/
missing list;
run;

See how you like the results.

[gamotte]

Hello,

with proc iml :

data have;
    input id fever vomiting redness swelling;
    cards;
1 1 0 1 1
2 1 1 0 0
3 0 1 1 1
4 1 0 1 0
5 0 0 1 1
;
run;

proc iml;
	use have(drop=id);
	   read all var _ALL_ into A[colname=varNames]; 
	close have;

	B=t(A)*A;

	create want from B[colname=varNames];
	append from B;
	close want;
quit;

[Ksharp]

The following would be better.

proc iml;
	use have(drop=id);
	   read all var _ALL_ into A[colname=varNames]; 
	close have;

	B=t(A)*A;

	create want from B[colname=varNames r=varNames];
	append from B[r=varNames];
	close want;
quit;

[ErikLund_Jensen · Accepted solution]

Hi @km0927 

If you don't have IML licensed at your site ($$$$), I think the only way is to rearrange data, so you have all occurrences of any two diagnoses per ID. Then you can get your wanted output with one proc freq. I tried and came up with this:

data have;
input id fever vomiting redness swelling;
datalines;
1    1          0          1             1
2    1          1          0             0
3    0          1          1             1
4    1          0          1             0
5    0          0          1             1
;
run;

proc transpose data=have out=temp1;
	by id;
run;

proc sql;
	create table temp2 as
		select a._name_ as diag1 label='', b._name_ as diag2 label=''
		from temp1 as a full outer join temp1 as b
		on a.id = b.id
		where a.col1 = 1 and b.col1 = 1;
quit;

proc freq data=temp2;
	table diag1 * diag2 / norow nocol nopercent;
run;

Here is the result. I left the totals out:

[Ksharp]

data have;
input id fever vomiting redness swelling;
datalines;
1    1          0          1             1
2    1          1          0             0
3    0          1          1             1
4    1          0          1             0
5    0          0          1             1
;
run;
proc corr data=have sscp noprint out=want(where=(_type_='SSCP' and 
_name_ ne 'Intercept') drop=intercept);
var fever vomiting redness swelling;
run;

[ErikLund_Jensen]

Kudos! - Your solution makes me feel like this:
https://www.shutterstock.com/da/image-vector/simple-cartoon-businessman-knocking-his-head-443053690

[Ksharp]

It doesn't matter. Actually I learned it from someone, Reeza ?

[km0927]

I really appreciate to all your help.

I choose ErikLund_Jesson as solution, because 'proc sql' was a bit more familiar to me.

Thanks.