topic Re: n*n table with multiple variables in SAS Programming

n*n table with multiple variables

km0927 — Thu, 14 Mar 2019 08:39:55 GMT

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?

Re: n*n table with multiple variables

andreas_lds — Thu, 14 Mar 2019 08:27:49 GMT

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.

Re: n*n table with multiple variables

km0927 — Thu, 14 Mar 2019 08:41:08 GMT

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

Re: n*n table with multiple variables

Astounding — Thu, 14 Mar 2019 09:24:45 GMT

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.

Re: n*n table with multiple variables

gamotte — Thu, 14 Mar 2019 09:37:52 GMT

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;

Re: n*n table with multiple variables

ErikLund_Jensen — Thu, 14 Mar 2019 10:18:04 GMT

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:

Re: n*n table with multiple variables

Ksharp — Thu, 14 Mar 2019 12:19:33 GMT

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;

Re: n*n table with multiple variables

Ksharp — Thu, 14 Mar 2019 12:30:33 GMT

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;

Re: n*n table with multiple variables

ErikLund_Jensen — Thu, 14 Mar 2019 13:54:16 GMT

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

Re: n*n table with multiple variables

Ksharp — Fri, 15 Mar 2019 13:03:23 GMT

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

Re: n*n table with multiple variables

km0927 — Mon, 18 Mar 2019 03:33:00 GMT

I really appreciate to all your help.

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

Thanks.