Unix SAS libname xlsx rounds numbers - I don't want it to

[PatMalarkey]

I have a SAS dataset with a column of percentages I calculate in my SAS (EG) program.  In my SAS dataset, pct has as many decimal places as it needs:

But when I use this code to write to Excel, pct gets rounded:

libname xlout  xlsx "/some/unix/directory/SomeExcelFile.xlsx";
data xlout.SomeSheet;
  set SomeInputFile;
run;

I'd like to keep the original number of decimal places and not round.

Any assistance would be greatly appreciated.  Thanks!

[Tom]

It is hard to tell from photographs what values are actually in the either place.

Pick one or two of the values which appear differently in Excel and show the values you started with in your SAS dataset.

For example run this and post the text from the SAS log so we can see what value you have for the first value of PCT in your SAS dataset.

data _null_;
  set have ;
  put pct = 32.20  / 'Hex value= ' pct hex16. ;
  stop;
run;

[PatMalarkey]

Hi, Tom!

Here is the log from using the code, below:

17 data _null_;
18 set SomeSASDataset ;
19 put pct = 32.20 / 'Hex value= ' pct hex16. ;
20 stop;
21 run;

pct=0.03005995341497030000
Hex value= 3F9EC8095358A8B0
NOTE: There were 1 observations read from the data set SomeSASDataset.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

[Tom]

I don't see any rounding.  Note there is always SOME rounding.  Just check the different HEX representation that SAS creates from using the decimal approximation as the source and the exact hex representation.

31    data test;
32      pct1=0.03005995341497030000;
33      pct2=input('3F9EC8095358A8B0',hex16.);
34
35      put (3*pct1 3*pct2) (=best32. :32.20 :hex16. /);
36    run;

pct1=0.03005995341497 0.03005995341497030000 3F9EC8095358A8AC
pct2=0.03005995341497 0.03005995341497030000 3F9EC8095358A8B0
NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


37
38    libname out xlsx 'c:\downloads\round.xlsx';
NOTE: Libref OUT was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: c:\downloads\round.xlsx
39    data out.test; set test; run;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: The data set OUT.test has 1 observations and 2 variables.
NOTE: The export data set has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


40    libname out clear;
NOTE: Libref OUT has been deassigned.

[PatMalarkey]

I don't know what to tell you - it rounds for me.  Could be because I am on Unix.

[Reeza]

What is the format on the variable when you export it? What version of SAS are you using? If it's really old, it may not have full XLSX support yet. 

I just tested Tom's code on the Linux box and no issues, it exports the whole value by default to an XLSX file. Even if I applied a format to the variable it still exported the entire value. 

 69            %put &sysscp. &sysscpl.;
 LIN X64 Linux
 70             proc product_status;run;
 
 For Base SAS Software ...
    Custom version information: 9.4_M6
    Image version information: 9.04.01M6P110718

[PatMalarkey]

Maybe Enterprise Guide, then?  EG connects to a linux box. 

I just explicitly coded format pct 20.18 in the data step where I create it and then ran the data step where I write to the XL file - no joy.

.

[Tom]

What version of SAS are you running?

If you are using the XLSX libname engine to create the XLSX file then the version of Enterprise Guide you are using to submit your SAS code should not matter.

[PatMalarkey]

Here is a snip from a log for a program that ran on our linux box this morning:

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M3)

NOTE: This session is executing on the Linux 2.6.32-696.30.1.el6.x86_64 (LIN X64) platform.

[Tom]

It is not Unix issue. 

What version of SAS are you running?

PC  
42    %put &=sysvlong ;
SYSVLONG=9.04.01M5P091317

Unix
23   %put &=sysvlong ;
SYSVLONG=9.04.01M5P091317

[PatMalarkey]

Please see my response to Reeza.

[Reeza]

Show what that value looks like in the formula bar. I think Excel is just adding some formatting of it's own that's overriding the values but the underlying values are still in the cell. Kind of like SAS's automatic formats.

You can switch to ODS EXCEL and apply your formats using proc report/print which will give you some more control over how the Excel file is created and how the values appear.

[PatMalarkey]

Hi, Reeza!

Here is a picture including the formula bar with the cursor in one of the pct cells:

I added a column called calc_pct - this is the work-around we're using until I can figure this out:

I will look into ODS EXCEL - ODS is not my strong suit, so I prefer not to use it.  This report is part of a multi-sheet workbook and I don't know if ODS will let me do that.

[Reeza]

---

@PatMalarkey wrote:

I will look into ODS EXCEL - ODS is not my strong suit, so I prefer not to use it.  This report is part of a multi-sheet workbook and I don't know if ODS will let me do that.

---

ODS offers more significantly more options to customize your workbooks than PROC EXPORT, such as the ability to add headers, filters, formatted tables, text, table of contents, linked formula cells and of course multiple sheets per workbook. ODS EXCEL also generates a native XLSX file, whereas historically TAGSETS.EXCELXP used to create an XML file that Excel could read.

[PatMalarkey]

I was able to use ODS Excel to write the sheet with the issue to Excel and the percent retains the correct format. I need to write multiple sheets to the workbook, so it's not a complete solution - though I was able to write the other sheets using LIBNAME.