topic Re: Unix SAS libname xlsx rounds numbers - I don't want it to in SAS Programming

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

PatMalarkey — Fri, 30 Apr 2021 17:42:50 GMT

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!

Re: libname xlsx rounds numbers - I don't want it to

Tom — Fri, 30 Apr 2021 17:46:10 GMT

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;

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

Reeza — Fri, 30 Apr 2021 17:55:03 GMT

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.

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

PatMalarkey — Fri, 30 Apr 2021 18:22:47 GMT

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.

Re: libname xlsx rounds numbers - I don't want it to

PatMalarkey — Fri, 30 Apr 2021 18:27:57 GMT

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

Re: libname xlsx rounds numbers - I don't want it to

Tom — Fri, 30 Apr 2021 18:44:46 GMT

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.

Re: libname xlsx rounds numbers - I don't want it to

PatMalarkey — Fri, 30 Apr 2021 18:47:57 GMT

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

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

Reeza — Fri, 30 Apr 2021 19:28:50 GMT

@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.

Re: libname xlsx rounds numbers - I don't want it to

Reeza — Fri, 30 Apr 2021 19:36:22 GMT

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

Re: libname xlsx rounds numbers - I don't want it to

Tom — Fri, 30 Apr 2021 19:37:54 GMT

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

Re: libname xlsx rounds numbers - I don't want it to

PatMalarkey — Mon, 03 May 2021 13:59:12 GMT

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.

Re: libname xlsx rounds numbers - I don't want it to

PatMalarkey — Mon, 03 May 2021 14:00:24 GMT

Please see my response to Reeza.

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

PatMalarkey — Mon, 03 May 2021 14:03:49 GMT

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.

Re: libname xlsx rounds numbers - I don't want it to

Tom — Mon, 03 May 2021 14:04:51 GMT

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.

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

Tom — Mon, 03 May 2021 14:15:10 GMT

@PatMalarkey wrote:
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.

So have we now moved away from being worried about the values being wrong and are now talking about how to control the "format" used in to display the numbers in the XLSX file?

Re: libname xlsx rounds numbers - I don't want it to

PatMalarkey — Mon, 03 May 2021 14:21:53 GMT

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.

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

PatMalarkey — Mon, 03 May 2021 14:23:42 GMT

No. The values are incorrect when I use the LIBNAME method. They get rounded and they should not be. ODS is one work-around, but it is less than optimal.

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

Reeza — Mon, 03 May 2021 15:13:49 GMT

ODS EXCEL supports multiple sheets and multiple tables per sheet.

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

Tom — Mon, 03 May 2021 15:23:59 GMT

@PatMalarkey wrote:

No. The values are incorrect when I use the LIBNAME method. They get rounded and they should not be. ODS is one work-around, but it is less than optimal.

We have shown that the XLSX engine does not round the sample value you sent.

So if it is still doing that for you (and you are using at least SAS 9.4M5) then open a support ticket with SAS support to have them help debug why your installation is not working. Perhaps the issue is only triggered with the right combination of data and not just the simple example value you posted.

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

PatMalarkey — Mon, 03 May 2021 16:05:08 GMT

I see that now! Thanks!

Link: https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-multiple-sheet/td-p/225377