topic Re: Proc freq output dataset missing the totals for a 2*2 table in SAS Programming

Proc freq output dataset missing the totals for a 2*2 table

Kc2 — Mon, 05 Nov 2018 15:29:52 GMT

Hello,

I am doing a simple 2*2 proc freq.

proc freq data= pop order=freq;
tables sex*severity /missing ;
run;

i get an output wiht the totals

Frequency| Severe | Attenuated| Undetermed | Total
. | 0 | 1 | 0 | 1
MALE | 371 | 183 | 34 | 588
FEMALE | 336 | 194 | 25 | 555
Total 707 378 59 1144

But when I specify an output dataset and a noprint option:

proc freq data= pop noprint order=freq;
tables sex*severity /missing out=test(drop=percent) ;
run;

the output dataset test does not have the cumulative total. I tried a few options but none work.

Any idea what I need to specify to get the total to be added in the output dataset?

thanks

Re: Proc freq output dataset missing the totals for a 2*2 table

PaigeMiller — Mon, 05 Nov 2018 15:40:57 GMT

You could get this using ODS OUTPUT. If there is a continuous variable in the data set, then PROC SUMMARY works as well.

ods output crosstabfreqs=freqs;
proc freq data=pop;
    tables sex*severity/missing;
run;

Re: Proc freq output dataset missing the totals for a 2*2 table

ballardw — Mon, 05 Nov 2018 15:42:28 GMT

@Kc2 wrote:

Hello,

I am doing a simple 2*2 proc freq.

proc freq data= pop order=freq;
tables sex*severity /missing ;
run;

i get an output wiht the totals

Frequency| Severe | Attenuated| Undetermed | Total
. | 0 | 1 | 0 | 1
MALE | 371 | 183 | 34 | 588
FEMALE | 336 | 194 | 25 | 555
Total 707 378 59 1144

But when I specify an output dataset and a noprint option:

proc freq data= pop noprint order=freq;
tables sex*severity /missing out=test(drop=percent) ;
run;

the output dataset test does not have the cumulative total. I tried a few options but none work.

Any idea what I need to specify to get the total to be added in the output dataset?

thanks

KC

You can try

proc freq data= pop noprint order=freq;
       tables sex*severity /missing norow nocol nopercent ;
       ods output crasstabfreq=test;
run;

Which will have the row and column totals but I suspect not in a form you want.

It is dangerous to include "totals" inside a data set when not extremely well documented, ie. another variable such as the _TYPE_ variable in the ODS OUTPUT table that indicates whether the value is a cell, row, column or table total.

Why do you need the totals in the data set?

Re: Proc freq output dataset missing the totals for a 2*2 table

Jagadishkatam — Mon, 05 Nov 2018 15:43:30 GMT

you could try

data want;
set pop;
output;
sex='Total';
severity ='Total';
output;
run;


proc freq data= want order=freq;
       tables sex*severity /missing out=test(drop=percent) ;
run;

Re: Proc freq output dataset missing the totals for a 2*2 table

Kc2 — Mon, 05 Nov 2018 16:06:15 GMT

I need to have the totals column so I am trying to do everything in one step..

So far none of the solutions provided work so i will do calculated the total in a separate step.

Re: Proc freq output dataset missing the totals for a 2*2 table

PaigeMiller — Mon, 05 Nov 2018 16:08:45 GMT

The PROC SUMMARY approach does provide column totals (and row totals).

Re: Proc freq output dataset missing the totals for a 2*2 table

ballardw — Mon, 05 Nov 2018 16:12:36 GMT

@Kc2 wrote:

I need to have the totals column so I am trying to do everything in one step..

So far none of the solutions provided work so i will do calculated the total in a separate step.

WHY? Do "what everything in one step"?

Re: Proc freq output dataset missing the totals for a 2*2 table

Kc2 — Mon, 05 Nov 2018 16:48:59 GMT

for reporting purposes. to report the individual categorical count and the tonal by category.

Re: Proc freq output dataset missing the totals for a 2*2 table

PaigeMiller — Mon, 05 Nov 2018 17:35:42 GMT

The idea of doing everything in one step sometimes leads people to very complicated or unworkable code that takes hours to get right.

You can run PROC SUMMARY to get the sums, and then PROC REPORT to make your nice report. You could also do it entirely in PROC REPORT.

Re: Proc freq output dataset missing the totals for a 2*2 table

ballardw — Mon, 05 Nov 2018 23:07:46 GMT

Proc tabulate if you don't like the default proc freq appearance is also an option.

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

Proc tabulate also has lots of appearance modification possibilities.