BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasiscool
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
sasiscool
Obsidian | Level 7

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

5 REPLIES 5
Cynthia_sas
SAS Super FREQ
** 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

sasiscool
Obsidian | Level 7
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
Super User

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
SAS Super FREQ

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

sasiscool
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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