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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.