The SAS Output Delivery System and reporting techniques

excelxp tagsets and pctn: I want to truncate values

Reply
Occasional Contributor
Posts: 6

excelxp tagsets and pctn: I want to truncate values

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.

Grand Advisor
Posts: 9,567

Re: excelxp tagsets and pctn: I want to truncate values

How about this ?

Code: Program

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

Occasional Contributor
Posts: 6

Re: excelxp tagsets and pctn: I want to truncate values

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.

Grand Advisor
Posts: 9,567

Re: excelxp tagsets and pctn: I want to truncate values

The only thing I can imagine is you need to enumerate all these integer . OR use proc fcmp + proc format to make it better.

Code: Program

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

Ask a Question
Discussion stats
  • 3 replies
  • 542 views
  • 0 likes
  • 2 in conversation