How do I add brackets in my column frequency and percentage when using PROC Freq with Chisq option

[DavidsM]

Dear SAS Community,

I have many categorical variables in my data, and I have been using the following analytic procedure to get my 2x2 results with column counts and percentages, but I would like to add brackets separating the counts from the percentages in the form of xx (%) instead of xx  %. Below is the code and sample view of the current outputs. 

/*Obtaining 2x2 counts and percentages for all categorical variables stratified by Age Category Columns */

Proc Freq Data=Analysis; Tables _Char_*Age_Category/Chisq norow nopercent;
run;

The above code generates results for the variables including column counts and percentages as follows (see sample below). 

Copying results from each cell and then adding brackets in the word file for each cell takes a lot of time and I would like to find a solution on how to add the brackets, for example, for the first cell to be produced as 9 (1.35).

I will be glad to receive assistance and sample code that I can apply to my data for such procedures. 

Thank you.

[PaigeMiller]

The %TABLEN macro can do this.

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

[DavidsM]

Dear PaigeMiller,

Thank you for the reply and for providing the resource. This is the exact output I am looking for, and I had seen this macro online and tried running the macro, but I was getting an error below, which I have seen again.

Below is the error I get after running the Marco. 

The % symbol is flagged as seen above.

How can I fix the error?

Thank you so much for your assistance.

[Tom]

You need to COMPILE the macro before trying to CALL it.

To compile a macro you can simply run the code that defines it.  From the %MACRO statement to the %MEND statement.

[PaigeMiller]

---

@DavidsM wrote:

The % symbol is flagged as seen above.

How can I fix the error?

---

You have to download the macro from that web page I linked to, and then compile the macro by running the SAS code of the macro, before you try to use it with %TABLEN

[Ksharp]

That need to change the template of proc freq, but that could cost lots of time.

And a workaround way is using proc report.

Proc Freq Data=sashelp.heart noprint;
Tables bp_status*sex/out=x outpercent Chisq norow nopercent;
run;

data x2;
 set x;
 value=cats(count,'(',put(PCT_COL,8.2),')');
 run;
 proc report data=x2 nowd;
 column bp_status value,sex;
 define bp_status/group;
 define sex/across;
 define value/group '';
 run;

[DavidsM]

Dear Ksharp,

Thank you so much for the assistance.

I have run the code you provided and reproduced the output as the one you shared.

I understand the nopercent option in the 2x2 tables excludes the percentage for the third column of totals. Is there any way the code you shared can be modified to include the column of totals and the percentage in brackets, as you have done for the stratifying variable column frequency and percentages? 

Once again, thank you so much for your assistance. I really appreciate your assistance.

Thank you. 

[Ksharp]

Nope. It is really uneasy for proc freq to do that, but easy for SQL.

Data have;
 set sashelp.heart;
run;

proc sql;
create table want as
select bp_status,sex,cats(count(*),'(',put(100*count(*)/(select count(*) from have where sex=a.sex),8.2),')') as value length=40 from have as a group by bp_status,sex
union
select bp_status,'Total',cats(count(*),'(',put(100*count(*)/(select count(*) from have),8.2),')') from have as a group by bp_status
union
select 'Total',sex,cats(count(*),'(',put(100*count(*)/(select count(*) from have),8.2),')') from have as a group by sex
union
select 'Total','Total',cats(count(*),'(100)') from have
;
quit;


 proc report data=want nowd;
 column bp_status value,sex;
 define bp_status/group;
 define sex/across;
 define value/group '';
 run;

[PaigeMiller]

Well, I have to respectfully disagree with @Ksharp that SQL is the "easy" way to do this, especially for real-world problems where there are lots of variables or lots of levels.

I do understand that %TABLEN may not be that easy either, but it will work on real-world problems with lots of variables or lots of levels, but you have to climb up the learning curve, just as you would with doing it in SQL. Once you learn it, then it will work for you on all future problems easily. With SQL, each time you want to use it on a new problem, you have to write new SQL.

In my opinion, the real easy solution is to not insist on a the poor display format of 1185(42.1). which I find difficult to read and for which the numbers in the columns don't align, making the numbers difficult to compare. Put the percents and counts side by side in two separate columns, on the same row, PROC REPORT makes that easy (example from my own work shown below where I have changed the column headings to protect confidentiality); OR keep them in the same cell as PROC FREQ does. Please also note that in the outputs from @Ksharp and the outputs from %TABLEN, the numbers are not vertically aligned, but in my output from PROC REPORT below, the numbers are vertically aligned.

Few people seem to care about making the report readable and clear; most people seem to care only about getting the program to work. I care about making the report readable and clear, and in my opinion, everyone should care about this. (And as long as I am on this subject of making reports readable and clear, counts greater than 999 should use the COMMA format, so 1,276 appears and not 1276 without the comma)

[Ksharp]

Paige,
I like your idea that put count and percent in two separated columns.
As a matter of fact , these reports have such regular or formal layout. E.X. FDA ask the submitted reports have such form(a.k.a put them together in a cell).
For a sas programmer, have nothing could to do, unless FDA changed the template of report.

[PaigeMiller]

Yes, that is true, in some fields the ridiculous formatting of xx(yyy) is required. But the OP didn't say that was the reason he needed that format. And in any case, for the people who have to use that format, in my opinion the %TABLEN macro is something worth learning.

[ballardw]

---

@PaigeMiller wrote:

Yes, that is true, in some fields the ridiculous formatting of xx(yyy) is required. But the OP didn't say that was the reason he needed that format. And in any case, for the people who have to use that format, in my opinion the %TABLEN macro is something worth learning.

---

I have had requests for similar formatting but I did them in Proc Tabulate using ods options to display the first column right justified, the second column left justified and removed the cell border between the columns (and some code for the column headers to be "nice" with such). The cell padding usually leaves a little space before the column starting with ( but can be reduced/increase with the cell padding settings.

But I don't know what file formats the FDA and similar use that may or may not reveal the trickery involved.

[dxiao2017]

Hi @DavidsM , what about picture format, you can try this:

/*prepare freq dataset*/
proc format;
   value agefmt low-45='<=45'
                46-high='>45';
run;
proc freq data=sashelp.heart noprint;
   tables bp_status*ageatstart/
             out=bpstat
             chisq norow nopercent;
   format ageatstart agefmt.;
run;
proc print data=bpstat;run;
/*use picture format to add bracket 
to percent column*/
proc format;
   picture pctfmt low-high='(09.99)'
                    (prefix='(');
data bpstat1;
   set bpstat;
   format percent pctfmt.;
run;
proc print data=bpstat1;run;
   

A question is I just need to add (prefix='(') option, because if I do not add prefix, there left part  '(' of the bracket is missing in the result:

proc format;
   picture pctfmt low-high='(09.99)';
data bpstat1;
   set bpstat;
   format percent pctfmt.;
run;
proc print data=bpstat1;run;