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%) |
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: