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?
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:
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.
I'm really sorry for my mistake. I corrected it.
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;
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;
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:
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;
It doesn't matter. Actually I learned it from someone, Reeza ?
I really appreciate to all your help.
I choose ErikLund_Jesson as solution, because 'proc sql' was a bit more familiar to me.
Thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.