topic Re: How do I create a frequency table with multiple row variables in SAS Programming

How do I create a frequency table with multiple row variables

JenMMerc — Sat, 04 Apr 2020 19:41:09 GMT

I have a data set with multiple variables for symptoms (headache 0,1; blurry vision 0,1). I'd like to create 3 tables-first a table with all vars by presence of symptom like this

	0	1
Headache
Hypertension
Blurry vision

a second table with all of the identified symptoms (symptom var=1) and col % for each.

	N	%
Headache
Hypertension
Blurry vision

and last a table with identified symptoms (symptom var=1) by hospitalized or not (hospital=0,1).

	Hospitalized
	0	1
Headache
Hypertension
Blurry vision

Thank you,

Jenny

Re: How do I create a frequency table with multiple row variables

ChrisNZ — Sat, 04 Apr 2020 22:31:55 GMT

Show us an example.

Say a dozen rows of input data, 3 output tables.

Provide the input data as a data step so we can run it (and check that what you posted can run).

Re: How do I create a frequency table with multiple row variables

JenMMerc — Sat, 04 Apr 2020 22:57:00 GMT

data new;

input patient_id headache fever blur_vision hospital;

datalines;

120 1 1 0 0

121 1 1 1 0

122 0 1 0 1

123 1 0 0 1

124 1 1 1 1

125 0 0 1 0

126 0 1 0 1

127 0 0 1 1

128 0 0 0 0

129 1 1 1 1

130 1 0 0 1

131 0 1 1 0

;

run;

Re: How do I create a frequency table with multiple row variables

Reeza — Sun, 05 Apr 2020 03:04:24 GMT

2 is quite easy and what you can do from there is get to 1 relatively easily.

NOBS is the total number of observations, SUM is the number that are 1, MEAN is the % (you will need to format it). Use a data step on the WANT output to get the other numbers you want.

proc means data=have noprint stackods NOBS SUM MEAN;
var headache fever blur_vision;
ods output summary = want;
run;

Re: How do I create a frequency table with multiple row variables

ChrisNZ — Sun, 05 Apr 2020 10:17:29 GMT

Here is one way.

T3 needs to be turned around. I let you do it.

proc summary data=HAVE ;
  class HOSPITAL;
  var HEADACHE -- BLUR_VISION ;
  output out=SUMMARY sum=;
run;

proc transpose data=SUMMARY out=TRANSPOSED; 
  by HOSPITAL;
run;

data T1(keep=_NAME_ NB_1 NB_0) 
     T2(keep=_NAME_ NB_1 PCT ) 
     T3(keep=_NAME_ NB_1 HOSPITAL ); 
  set TRANSPOSED  ;
  retain TOTAL;
  if _NAME_='_FREQ_' then TOTAL=COL1;
  if _NAME_ ne: '_' ;
  NB_1=COL1; NB_0=TOTAL-COL1; PCT=NB_1/TOTAL;
  if HOSPITAL=. then output T1 T2;
  else output T3;
run;


NAME	NB_1	NB_0
HEADACHE	6	6
FEVER	7	5
BLUR_VISION	6	6


NAME	NB_1	PCT
HEADACHE	6	0.5
FEVER	7	0.583333
BLUR_VISION	6	0.5


HOSPITAL	NAME	NB_1
0	HEADACHE	2
0	FEVER	3
0	BLUR_VISION	3
1	HEADACHE	4
1	FEVER	4
1	BLUR_VISION	3