topic excelxp tagsets and pctn: I want to truncate values in ODS and Base Reporting

excelxp tagsets and pctn: I want to truncate values

saul_kanowitz_cdph_ca_gov — Tue, 30 Jun 2015 20:27:09 GMT

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.

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

Ksharp — Wed, 01 Jul 2015 14:23:17 GMT

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

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

saul_kanowitz_cdph_ca_gov — Wed, 01 Jul 2015 20:18:32 GMT

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.

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

Ksharp — Thu, 02 Jul 2015 12:15:43 GMT

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