topic Re: Proc Tabulate- Counts and Percentages within a Single Cell in SAS Programming

Proc Tabulate- Counts and Percentages within a Single Cell

Macromore — Mon, 22 Jun 2020 19:46:16 GMT

I currently generating a report using some descriptive statistics within Proc Tabulate where I generate the counts and percentages to produce an Excel output using ODS. Originally, I would produce a single cell for each value generated from Proc Tabulate, but I've received a request to combine the values into a single cell within the Excel output. For example, the output would contain n (%) within one cell. The data structure contains a record for each id with a designated cohort flag to identify the respected group. I've found that you can add the parentheses around the percentages using Proc Format along with the "prefix" option, but I'm having issues figuring out if it is possible to combine the two cells while maintaining an Excel output.

I realize it isn't ideal to have two values within a single Excel sheet, but the original program incorporates Proc Tabulate to generate the statistics for the outputs and I would like to avoid running it in Proc Report if possible.

This is an example of the current output:

	Overall	Disease 1	Disease 2	Disease 3
N (count)	3,000	1,000	1,500	500
Male	1,350	450	675	225
%	45.00%	45.00%	45.00%	45.00%
Female	1,650	650	825	275
%	55.00%	55.00%	55.00%	55.00%

This is an example of the desired output:

	Overall	Disease 1	Disease 2	Disease 3
N (count)	3,000	1,000	1,500	500
Male N (%)	1,350 (45.00%)	450 (45.00%)	675 (45.00%)	225 (45.00%)
Female N (%)	1,650 (55.00%)	650 (55.00%)	825 (55.00%)	275 (55.00%)

Re: Proc Tabulate- Counts and Percentages within a Single Cell

Nihanta — Mon, 22 Jun 2020 21:56:02 GMT

How about combining fields outside proc tabulate? Some of report requests are based on visual appeal and convenience but make little sense while programming. Format both values, combine as text fields and then use tabulate.

Re: Proc Tabulate- Counts and Percentages within a Single Cell

Reeza — Mon, 22 Jun 2020 22:04:08 GMT

AFAIK that can't be done. You need to use PROC REPORT or PRINT because it's now a character field, not a summarized and dynamic field. Or you could play around with modifying the template but that's not easy and I don't know if it's technically even possible.

Re: Proc Tabulate- Counts and Percentages within a Single Cell

ballardw — Mon, 22 Jun 2020 22:34:29 GMT

Define "in a single cell"?

Does it have to be a cell that excel recognizes or just appear that way?

Tabulate will only create a single value in any any cell though a format might make it appear to be two values.

proc format;
picture myfakepct  
low-high = '009.99%)' (prefix='(');
run;
proc tabulate data = sashelp.class;
   class sex age;
   table sex,
         age*(n=' '*[style=[just=r borderrightwidth=0]] 
              pctn=' '*[style=[borderleftwidth=0 just=l]]*f=myfakepct.
             )
         / misstext=' '
   ;
run;

You did not provide any example code for how you currently created percent values or applied a format.

The values of the PCTN format are actually a fw.d type format so you would need to fake a % sign if desired. The custom format fakes that for this data.

The style elements borderleftwidth and borderrightwidth set to 0 will suppress a local vertical border when applied to the correct sides of the cell, the Just= option moves the N and Pctn values closer to each each other.

A side affect of this approach is that the N will be above one another with typical alignment of the lowest signficance digit. If you create a single character value alignment might become more interesting and unless you are using a format Tabulated won't show them in the body of the table.

There is also "demographics report" macro floating around this site that I don't have bookmarked that may be of more interest.

Re: Proc Tabulate- Counts and Percentages within a Single Cell

Reeza — Mon, 22 Jun 2020 22:39:57 GMT

There's this one but it's definitely overkill for what OP is doing.
https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030

A quick PROC FREQ and REPORT is easier.

The code here does something similar as well.
https://gist.github.com/statgeek/0c4aeec9053cf8050be18a03b842c1b9