topic Re: apply a number format to a count of a character variable in proc Report in ODS and Base Reporting

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

sasiscool — Wed, 03 Aug 2016 17:03:34 GMT

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.

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

Cynthia_sas — Thu, 04 Aug 2016 00:40:23 GMT

** 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

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

sasiscool — Thu, 04 Aug 2016 14:32:36 GMT

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.

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

ballardw — Thu, 04 Aug 2016 17:42:19 GMT

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.

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

Cynthia_sas — Thu, 04 Aug 2016 22:40:42 GMT

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:

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

sasiscool — Mon, 08 Aug 2016 18:19:37 GMT

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.