topic Re: Suppressing a column in a proc frequently table in ODS and Base Reporting

Suppressing a column in a proc frequently table

greg6363 — Tue, 12 Feb 2019 16:58:48 GMT

I created a frequency table in the following form:

No (0) Yes (1) Total

Group A. 16 27 43
Group B. 25. 67 92
Group C. 37 91 128

Since I only wanted to see the Yes (1) column in the output,
I used a where statement in proc freq to remove the no (1) column.

However, this is how I want the final table to look:

Yes(1) Total

Group A. 41 %
Group B. 62 %
Group C. 88 %

In PROC FREQ, both columns produce the exact same values.
I want the output to show the percentage of the total dataset including the column that is not included in the output. So what I need to do is create a variable for a new column with the following calculation: Pct = Yes(1) / Total (for each group)

I couldn’t get it to work in PROC FREQ but is there a way to do it in PROC REPORT or PROC TABULATE?

Any assistance would be greatly appreciated. Thanks
.

Re: Suppressing a column in a proc frequently table

Reeza — Tue, 12 Feb 2019 22:34:02 GMT

A quick way is to capture the PROC FREQ output, filter it using a WHERE and then display the results using PROC PRINT.

@greg6363 wrote:
I created a frequency table in the following form:

No (0) Yes (1) Total

Group A. 16 27 43
Group B. 25. 67 92
Group C. 37 91 128

Since I only wanted to see the Yes (1) column in the output,
I used a where statement in proc freq to remove the no (1) column.

However, this is how I want the final table to look:

Yes(1) Total

Group A. 41 %
Group B. 62 %
Group C. 88 %

In PROC FREQ, both columns produce the exact same values.
I want the output to show the percentage of the total dataset including the column that is not included in the output. So what I need to do is create a variable for a new column with the following calculation: Pct = Yes(1) / Total (for each group)

I couldn’t get it to work in PROC FREQ but is there a way to do it in PROC REPORT or PROC TABULATE?

Any assistance would be greatly appreciated. Thanks
.

Re: Suppressing a column in a proc frequently table

Astounding — Tue, 12 Feb 2019 18:31:41 GMT

For a 0/1 variable, the % of 1's will be the mean of the variable. Just use (guessing at your variable names here):

proc means data=have mean;

var flagvar;

class group;

run;

Re: Suppressing a column in a proc frequently table

greg6363 — Tue, 12 Feb 2019 20:48:14 GMT

I should have been clearer in my question. I followed Reeza’s suggestion and put the where statement within the PROC PRINT procedure. In addition, I went back to the PROC FREQ procedure and used an OUT= statement to create a dataset out of the frequency table but the TOTAL column is not included in the output dataset. How can I get that TOTAL column to be included in the output data set?

Re: Suppressing a column in a proc frequently table

Reeza — Tue, 12 Feb 2019 20:52:01 GMT

I think it's time to show your work.

Re: Suppressing a column in a proc frequently table

greg6363 — Tue, 12 Feb 2019 21:05:03 GMT

Good idea. Here is the code:

PROC FREQ data=sample1 order=freq;
table group*status/ norow nocol out=sample2
plots=freqplot(type=dot);
run;

PROC PRINT data=sample2
where status=1;
run;

When the sample2 output data file is generated, the totals are not included which I need to include.

Re: Suppressing a column in a proc frequently table

Reeza — Tue, 12 Feb 2019 22:09:32 GMT

I don't think you want totals, I think you want the percents. Add the OUTPCT option to the TABLE statement to get those as well.

The PROC MEANS solution mentioned by another would also work.

Re: Suppressing a column in a proc frequently table

greg6363 — Tue, 12 Feb 2019 22:25:09 GMT

No, I want the Total column produced in the frequency table. It’s not being produced in the output data set.

Re: Suppressing a column in a proc frequently table

Reeza — Tue, 12 Feb 2019 22:34:38 GMT

I'm working off your WANT data set in your original post. There's no total shown there. If your requirements have changed please show the newly desired data set please.

Re: Suppressing a column in a proc frequently table

greg6363 — Tue, 12 Feb 2019 22:49:35 GMT

I put a Total column in my original output. This is what the PROC FREQ procedure produces:

No (0). Yes(1). Total

Group A. Freq. 24858. 948. 25806
Pct. 53.45. 2.04. 55.49
Group B. Freq. 6394. 700 7094
Pct. 13.75. 1.51. 15.25
Group C. Freq. 3491. 514. 4005
Pct. 7.51. 1.11. 8.61
Group D Freq. 3625. 355. 3980
Pct. 7.80. 0.76. 8.56

When I use the WHERE statement in PROC PRINT, it will remove either column but when I use that option, I still want to see the Total column that will reflect the overall number of observations in the data set compared to the printed condition.

Re: Suppressing a column in a proc frequently table

Reeza — Wed, 13 Feb 2019 17:03:16 GMT

@greg6363 wrote:

However, this is how I want the final table to look:

Yes(1) Total

Group A. 41 %
Group B. 62 %
Group C. 88 %

This is what I'm referring to. If this has changed, post what you want the new output table to look like.