apply a number format to a count of a character variable in proc Report

[sasiscool]

If I have code that looks like this:

proc report data=work.test;
column dinner Year

define dinner / group  f=$diner. ' ';

define year/across ' ' ;

run;

and produce a report

             2001      2002      2003

Tim's    90          1000       2312   

Ferris'   19          20          89

etc.....

I want to format the numbers to appear with commas, such as in the classics f=comma8. However, diner is a character variable, and unfortunately year is as well. Is there any way to apply number formats to counts of character variables, without having to convert the base variables to numeric?

Thank you.

[Cynthia_sas]

** make data into thousands;
** to use comma format;
data heart;
  set sashelp.heart;
  output; 
  output;
run;
   
proc report data=heart;
  column chol_status n,bp_status ;
  define chol_status / group;
  define bp_status / across;
  define n / 'Count' f=comma6.;
run;

Yes, if you explicitly use the N statistic in the report, you can format the number. for example, see the results below.

cynthia

[sasiscool]

Thanks for replying Cynthia.

That is how I used to have it, but it then it gives me two rows as headers, and the first cell is merged. This is a part of an ODS to excel, and I need to have the first row of across variables be in one row.

I think it is just a trade off, either manual work to changed the numbers to the correct format in excel, or manual work deleting rows to ensure I only have one. Oh well.

[ballardw]

If you do not need the Proc Report ability to calculate from other column results this may be possible with Proc Tabulate.

If I understand what you are attempting this may work:

proc tabulate data=work.test;
   class year dinner;
   format dinner $diner.;
   table dinner='', /* the ='' suppresses the variable or statistic label*/
         year=''*n=''*f=comma8.
   ; /* the *f=comma8. says to apply the comma8. format to the n statistic*/
run;

There is also an option available that would put a column header in the empty box above the dinner variable. Amazingly that option is BOX and would go before the semicolon ending the TABLE statement. Somelike  / BOX="some text";  The / is to indicate the start of table options. Or /Box=Dinner would place the label or variable name for dinner there.

[Cynthia_sas]

you can get rid of all those headers. Consider this change to the DEFINE statements:

define bp_status / ' ' across;
define n / ' ' f=comma6.;

That should leave you with just one column header row.

cynthia

The above would work for HTML or RTF or PDF, but for EXCELXP or ODS EXCEL, you'd need to use TAGATTR to get the commas:

[sasiscool · Accepted solution]

thank you so much, I was not aware i could force 1 column with the style clause like that. I have to read up on it.