The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

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.


Accepted Solutions
Solution
‎08-08-2016 02:19 PM
Occasional Contributor
Posts: 16

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

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.

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,719

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

[ Edited ]
** 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.

comma_format.png



cynthia

Occasional Contributor
Posts: 16

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

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.
Grand Advisor
Posts: 10,210

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

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.

 

SAS Super FREQ
Posts: 8,719

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

[ Edited ]

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:

one_header.png

Solution
‎08-08-2016 02:19 PM
Occasional Contributor
Posts: 16

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 319 views
  • 0 likes
  • 3 in conversation