BookmarkSubscribeRSS Feed
Eva
Quartz | Level 8 Eva
Quartz | Level 8

Dear all,

 

I have a proc report in an html output. Now I'd like to do the same in ods excel. There I have a problem with the format commax12.2 in a define statement of a proc report:

 

 

ods excel file="C:\test.xlsx";

proc report data=work.mydata;
  columns one two;
  define one / group;
  define two / analysis sum format=commax12.2;
run;

ods excel close;

 

 

 

The programm runs fine if I leave out the format.

The whole program also runs fine if I run it in ods html.

 

Can anybody tell which format to use under ods excel?

 

Best wishes

Eva

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not sure about ods excel, but in ods tagsets.excelxp you would do:

ods excel file="C:\test.xlsx";

proc report data=work.mydata;
  columns one two;
  define one / group;
  define two / analysis sum format=commax12.2 style={tagattr="format:###,###,###"};
run;

ods excel close;
Eva
Quartz | Level 8 Eva
Quartz | Level 8

Dear RW9,

 

unfortunately not.

I previously used ods tagsets.msoffice2k and then format=best12.2 would work. But also not in ods excel.

 

Best wishes

Eva

 

Reeza
Super User

TAGATTR should work in ODS Excel. 

Are you saying that's not working? If not, please post your code. 

 

 Edit: I have vague recollections of having to apply BOTH the SAS format and the TAGATTR statement to get the format to work correctly in ODS Excel. 

Tom
Super User Tom
Super User

So this code works fine.

ods excel file="C:\downloads\test_format.xlsx";

proc report data=sashelp.cars;
  columns make weight;
  define make / group;
  define weight / analysis sum format=comma12.2  style={tagattr="format:#,##0.00"};
run;

ods excel close;

To produce normal US formatting of numbers with periods for decimal place and commas for thousands separators.

 

Are you asking what is the Excel format to put into the TAGATTR style to get Excel to display the numbers using comma as decimal point and period as thousands separator?  Perhaps if you try formatting a cell in Excel and look and see what format Excel attaches to the cell?

 

If you are not also sending the report to other ODS destinations then you can change the FORMAT used to print the value from COMMA12.2 to something else.  For example you could use 32.2 instead if you are worried that 12 characters counting periods and commas and decimal digits is going to be enough for the size of your possible values.

 

 

Eva
Quartz | Level 8 Eva
Quartz | Level 8

Dear all,

 

thanks for your answers. With them I was now able to code the right format. This is it (just the define statement):

 

define two / analysis sum format=commax12.2 style={tagattr="format:###,###.##"};

I did indeed forget the format=commax12.2 with Excel. It is really needed.

 

And I chenged your suggestion for the tagattr so that I get the German format commax12.2

 

Best wishes

Eva

RW9
Diamond | Level 26 RW9
Diamond | Level 26

So, exactly what both myself and @Tom posted, but you mark your own post as answer.

Tom
Super User Tom
Super User

Can you explain in more detail what you mean about the FORMAT= option being needed with Excel?

 

Is the FORMAT=commaX12.2 needed to insure that Excel reads the numbers properly? If you use FORMAT=comma12.2 does Excel interpret the numbers as character strings? What about if you just use FORMAT=12.2 instead? Does Excel read the numbers properly?  What if you used the FORMAT=numX12.2?

 

Or is FORMAT=commaX12.2 needed so that your OTHER active ODS destinations are displaying the formatted numbers as you want?

 

What about the Excel format that you are using.  Do you need to use style={tagattr="format:###,###.##"} or do you get the same results if you just use style={tagattr="format:#,###.##"}?  And does it display the decimal places and thousand separators properly in Excel using this format?  That is does Excel know you want to see 1.234,56 even though the format style is written as #,###.##?

 

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
  • 7 replies
  • 4031 views
  • 0 likes
  • 4 in conversation