I have a situation where I am sending proc tabulate output to xml files. I have pctn as one of my columns. I have the following code to create a value with one number after the decimal place:
style=[tagattr='format:##0.0' just=r cellwidth=1in];
The problem is that excel is rounding up from two positions after the decimal point and I want to truncate. For example I have a pctn value of 3.35 that is being exported to excel as 3.4. I want it to export as 3.3.
Is there any code I can put in SAS to have excel truncate instead of rounding the values.
How about this ?
proc format;
picture fmt
low-high='009.9';
run;
ods tagsets.excelxp file='/folders/myfolders/x.xml';
proc tabulate data=sashelp.class;
class sex;
var weight;
table sex,weight*pctn*format=fmt. ;
run;
ods tagsets.excelxp close;
Xia Keshan
Message was edited by: xia keshan
I tried that and it helps with truncation but then a new problem comes into play. I want to have one digit printed after the decimal point, but if the number is an integer, then the decimal point and the zero after the decimal point don't print. That is why I used the
style=[tagattr='format:##0.0' just=r cellwidth=1in] code to hold the decimal point and the trailing zero.
It seems the merging of the style and the proc format is what is needed, but I can't figure out how to do that.
The only thing I can imagine is you need to enumerate all these integer . OR use proc fcmp + proc format to make it better.
proc format;
picture fmt
45,46,47,48,49,50,51,52,53,54,55='09'
other='09.9';
run;
data class;
set sashelp.class end=last;
output;
if last then do;output;end;
run;
ods tagsets.excelxp file='/folders/myfolders/x.xml';
proc tabulate data=class;
class sex;
var weight;
table sex,weight*pctn*format=fmt. ;
run;
ods tagsets.excelxp close;
Xia Keshan
Xia Kesan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.