topic Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point in SAS Programming

Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point

A_Kh — Tue, 21 Feb 2023 00:12:29 GMT

Dear Community,

While writing proc report output to Excel(xml) sheet, values ending in 0 after the decimal point gets rounded up. Eg. 0.19 written as 0.19, but 0.20 gets rounded to 0.2 (I need it as 0.20)
I used format=4.2 in define statement, as well as in the prior data steps. Tried a different ways. But couldn't get the desired result (in html output it is produced though).

Wondering if there is any ODS excel options to write the values from proc report as is.
I've checked a similar post at communities - https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-report-to-Excel-numbers-get-rounded-up/td-p/580562 , where @Tom answered the question. However, this answer is not resolving my issue.

Any tips and advice would be much appreciated!

Thanks and regards!

Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point

IyenJ — Tue, 21 Feb 2023 01:42:26 GMT

I'm not sure about the ODS EXCEL options. But, here's some additional tips about the ODS ExcelXP tagset.

First, you need to specify the style= option as a column style attribute. Additionally, make sure you specify the correct numeric format in the TAGATTR clause of the style= option.

For example, a 4.2 numeric format would translate into

Define SUM / Style(column)={TAGATTR='format:0.00'}

Alternately, a 6.2 numeric format would translate into

Define SUM / Style(column)={TAGATTR='format:000.00'}

Hope this helps.

Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point

PaigeMiller — Tue, 21 Feb 2023 13:57:51 GMT

I think you just need a format with two digits after the decimal. If you are not getting the desired output, you should provide us with your exact code, and also provide a portion of your data as working SAS data step code (and not in any other format).

data have;
    y=0.19; output;
    y=0.2; output;
run;

ods excel file="text.xlsx";
proc report data=have;
    columns y;
    define y/display format=7.2;
run;
ods excel close;

Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point

A_Kh — Tue, 21 Feb 2023 14:46:26 GMT

@IyenJ ,

Actually it was ODS tagsets.excelxp destination. And column(style) with tagattr=format works perfectly there. Thank you so much!

Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the decimal point

A_Kh — Tue, 21 Feb 2023 14:57:17 GMT

@PaigeMiller ,

Thank you for your input! I should have specified that was tagsets.excelxp destination, not excel itself. Unsure about how much different are options for those two destinations, I applied format 4.2 to the column in define statement(define Mean/ "Mean" center width=8 format=4.2 display;) , which rounded up the last decimal in xml file..

ods tagsets.excelxp options (sheet_name= "Table 7." start_at= "B3" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" Row_Heights='0,0,0,32,32,0,0' Skip_Space= '0,0,0,0,1');
	proc report data= items_final style(report)={background=white borderbottomcolor=black borderbottomwidth=1}
								 style(header)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1};
		column ITEM LABEL N MEAN SD cat0 cat1 cat2 cat3 cat4 cat5 Missing;
		define ITEM/ "Item ID" left width=10 display;
		define LABEL/ "Item Content" left width=30 display;
		define n/ "Valid n" center width=8 display;
		define Mean/ "Mean" center width=8 Style(column)={TAGATTR='format:0.00'} display;
		define SD/ "SD" center width=8 Style(column)={TAGATTR='format:0.00'} display;
		define cat0/ "0" center width=11 display;
		define cat1/ "1" center width=11 display;
		define cat2/ "2" center width=11 display;
		define cat3/ "3" center width=11 display;
		define cat4/ "4" center width=11 display;
		define cat5/ "5" center width=11 display;
		define Missing/ "Missing" center width=11 display;
	run; 
	title;footnote;