How to use to crate a Freq table of One variable with multiple variable

[hjjijkkl]

I want to create one freq table contains  (ethnicity gender sex) by ARM. 

I tried the code below but, it is giving me 3 freq tables for each variable with (ARM by ethnicity , ARM by gender and ARM by sex) . Bit, i want one table that contains ARM by ethnicity gender sex. 

proc freq data=want;

tables ARm *(ethnicity gender sex);
run;

[Reeza · Accepted solution]

Use PROC TABULATE instead or you need to manually massage the output from PROC FREQ in a data step. 

proc tabulate data=want;
class arm ethnicity gender sex;
table (ethnicity gender sex), arm*N;
run;

If you want percentages look at the examples in the documentation for the correct references on what to specify in the code. FYI - I would have to look it up to answer it for you...it's particular.

This is an example of how the manual manipulation of PROC FREQ could work. You would need to modify it very slightly to get it to work for you, I think just the keep statement. 

ods output crosstabfreqs=summary;
proc freq data=sashelp.class;
table sex*(_all_);
run;



data long;
	length variable $32. variable_value $50.;
	set summary;
	Variable=scan(table, 2, '*');
	Variable_Value=strip(trim(vvaluex(variable)));
	presentation=catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
	keep sex variable  variable_value frequency percent presentation;
	label variable='Variable' variable_value='Variable Value';
run;

And one other option, this macro is quite useful:

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

---

@hjjijkkl wrote:

I want to create one freq table contains  (ethnicity gender sex) by ARM. 

I tried the code below but, it is giving me 3 freq tables for each variable with (ARM by ethnicity , ARM by gender and ARM by sex) . Bit, i want one table that contains ARM by ethnicity gender sex. 

proc freq data=want;

tables ARm *(ethnicity gender sex);
run;

---

[hjjijkkl]

using the macro might be a lot easier but, i am getting this error in the log. Not sure how to solve it.
%tablen(data=want, by=arm,
-
180
WARNING: Apparent invocation of macro TABLEN not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order

[SASKiwi]

@hjjijkkl  - You need to download the macro program and run it before calling it - there is a download link in the Community article.

[hjjijkkl]

I do want the frequency and the counts in the table 

[mkeintz]

---

@hjjijkkl wrote:

I do want the frequency and the counts in the table 

---

What do you mean?  I understand frequency and counts to mean the same thing.

[hjjijkkl]

Still having error on the log after downloading the macro. I am not familiar with macro. Any other way to have crate frequency table of one to many (x * mult variable)  with the results in one table instead of separate tables? 

[Reeza]

I offered three suggestions, none of the others work?

[hjjijkkl]

the Macro I am not able to solve and the first suggestion doesnt give me freq, percentage and total. the third suggestion works but, it put the result in sperate tables. I wanted to result to be displayed in one table

[Reeza]

The second suggestion (freq + data step) puts it in a single table called want. Did you examine that table? It wasn't printed by default.

The first suggestion can be modified to include percentages and totals. Add totals with the ALL keyword and I referred you to where to look up the percentages.
All is shown below. If you try the percentages portion and can't figure it out, post the code you've tried and we'll be happy to help you.

proc tabulate data=want;
class arm ethnicity gender sex;
table (ethnicity all) (gender all) (sex all), (arm all)*N;
run;

[ballardw]

---

@hjjijkkl wrote:

the Macro I am not able to solve and the first suggestion doesnt give me freq, percentage and total. the third suggestion works but, it put the result in sperate tables. I wanted to result to be displayed in one table

---

Proc freq when you use var1*var2 produces 4 different percentages: percent of row, percent of column, percent of table and the marginal row/column total percentages. Which do you want?

Really you should provide some example data, a small set works as long as it demonstrates all the needs you have, and what that result should look look like.

Proc Tabulate can calculate 5 different percentages for Class variables (plus can use the count of a second variable as denominator). So we need to know which percentage you want. Also similar percentages involving Var plus percentages of SUMS.

Please do not make us drag your requirements out one-by-one as you say "but it doesn't do xxxx" when you have told us that you have a requirement for "xxx".

[hjjijkkl]

This is an just an example and The numbers in the table might make nonsense. But, this how I would want to out put to look like

variable	Category	Arm_1	Arm_2	Total
sex	Female	1/45 (2)	3/86 (3.5)	4/131 (3)
	Male	23/23 (100.0)	54/58 (93.1)	80/84 (95.2)
Race	Black	1/45 (2)	3/86 (3.5)	4/131 (3)
	white	6/45 (100.0)	7/86 (93.1)	13/131 (9.9)
	Hispanic	6/45 (2)	9/86 (3.5)	15/131 (11.5)

[ballardw]

---

@hjjijkkl wrote:

This is an just an example and The numbers in the table might make nonsense. But, this how I would want to out put to look like

variable	Category	Arm_1	Arm_2	Total
sex	Female	1/45 (2)	3/86 (3.5)	4/131 (3)
	Male	23/23 (100.0)	54/58 (93.1)	80/84 (95.2)
Race	Black	1/45 (2)	3/86 (3.5)	4/131 (3)
	white	6/45 (100.0)	7/86 (93.1)	13/131 (9.9)
	Hispanic	6/45 (2)	9/86 (3.5)	15/131 (11.5)

---

For Proc tabulate to do that you would as a minimum have to have a single variable ARM with values of 1 and 2.

You should be able to run this code as the SASHELP.Class data set should be in your installation:

proc tabulate data=sashelp.class;
   class sex age;
   table sex,
         (age all='Total') *(n rowpctn)
         /
   ;
run;

Which behaves somewhat like your first table. The Class data set doesn't have another good grouping variable.

Proc Tabulate will not display a variable name or label in a separate column, that would require an additional variable and a nesting .

[hjjijkkl]

How can i rename the ARM_1 and ARM_2 to blood pressure and placebo respectively  and the same with sex 1 to male and sex 2 to female in proc tabulate?

[Reeza]

I would recommend using formats. There's a good paper titled 'Not Just Another Pretty Face' that illustrates how to use formats. if you're not familiar with them.