BookmarkSubscribeRSS Feed
saul_kanowitz_cdph_ca_gov
Calcite | Level 5

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.

3 REPLIES 3
Ksharp
Super User

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

saul_kanowitz_cdph_ca_gov
Calcite | Level 5

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1160 views
  • 0 likes
  • 2 in conversation