topic Re: How to use to crate a Freq table of One variable with multiple variable in New SAS User

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

hjjijkkl — Thu, 08 Apr 2021 22:34:02 GMT

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;

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

Reeza — Thu, 08 Apr 2021 23:02:42 GMT

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-TABLEN/ta-p/634030

@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;

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

hjjijkkl — Thu, 08 Apr 2021 23:19:58 GMT

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

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

hjjijkkl — Thu, 08 Apr 2021 23:20:52 GMT

I do want the frequency and the counts in the table

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

mkeintz — Thu, 08 Apr 2021 23:45:20 GMT

@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.

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

SASKiwi — Fri, 09 Apr 2021 01:32:04 GMT

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

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

hjjijkkl — Fri, 09 Apr 2021 15:37:37 GMT

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?

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

Reeza — Fri, 09 Apr 2021 15:53:37 GMT

I offered three suggestions, none of the others work?

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

hjjijkkl — Fri, 09 Apr 2021 16:07:46 GMT

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

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

Reeza — Fri, 09 Apr 2021 16:18:47 GMT

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;

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

ballardw — Fri, 09 Apr 2021 16:22:13 GMT

@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".

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

hjjijkkl — Fri, 09 Apr 2021 17:16:08 GMT

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)

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

ballardw — Fri, 09 Apr 2021 20:28:30 GMT

@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)

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 .

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

hjjijkkl — Mon, 12 Apr 2021 16:54:15 GMT

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?

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

Reeza — Mon, 12 Apr 2021 17:53:29 GMT

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.