topic Re: Losing significant digit with Libname Temp xlsx '' in SAS Enterprise Guide

Losing significant digit with Libname Temp xlsx ''

alepage — Thu, 17 Sep 2020 13:17:14 GMT

Hello,

How to keep the significant digit (numerical variable) while copy the data set into an Excel File.

%let testing1=/f..../me;
libname report xlsx "&testing1./Temp7.xlsx";

data report.today;
set test;
run;

Does someone know if there is something to do to avoid to lose my significant digits for var5 et var6

Regards,

Re: Losing significant digit with Libname Temp xlsx ''

PaigeMiller — Thu, 17 Sep 2020 13:39:11 GMT

How many significant digits do you want to retain? There is a limit that you cannot go beyond, I think it is 15 digits.

Re: Losing significant digit with Libname Temp xlsx ''

alepage — Thu, 17 Sep 2020 13:46:32 GMT

format 15.2 so only two significant digits.

Re: Losing significant digit with Libname Temp xlsx ''

PaigeMiller — Thu, 17 Sep 2020 13:49:05 GMT

Are you talking about round-off error and not significant digits?

Please provide an example so we can see what you are seeing. We need to see the input data, and the result that you think is not correct. Do not attach an Excel file, as most people including me will not download it.

Re: Losing significant digit with Libname Temp xlsx ''

alepage — Thu, 17 Sep 2020 14:14:16 GMT

Hello,

I will make a test with a dataset from sashelp to check if it is an issue with Excel or SAS.

I will get back to you shortly.

Regards

Re: Losing significant digit with Libname Temp xlsx ''

ballardw — Thu, 17 Sep 2020 14:27:12 GMT

What is the current FORMAT assigned to those variables in your Test dataset?

How many "significant" digits appear?

Do you have trailing 0 for decimal values that you consider significant?

Re: Losing significant digit with Libname Temp xlsx ''

alepage — Thu, 17 Sep 2020 14:28:47 GMT

Hello,

The first data set that I have is like the following one (partial dataset)

Var1	Var2
4426.00	1.00
5644.00	2.00
19569.00	4.00
3362.00	1.00
14427.00	1.00
10643.00	1.00
2490.00	1.00
4471.00	1.00

When I execute the following code

data report.cnts;
set test;
run;

What I have in Excel is:

Var1	Var2
4426	1
5644	2
19569	4
3362	1
14427	1
10643	1
2490	1
4471	1

This is more an Excel issue, I believe.

I have made the same test but with sashelp.class and I am getting the same value in Excel than those into the dataset.

I think that Excel reads the first 200 rows to see the format and as in my dataset all those values have .00 , Excel round up all values.

Regards,

Re: Losing significant digit with Libname Temp xlsx ''

Kurt_Bremser — Thu, 17 Sep 2020 15:18:04 GMT

I see no loss of digits here, the values match exactly. The only thing that is lost is formatting.

Which display format is used in SAS?

Re: Losing significant digit with Libname Temp xlsx ''

ballardw — Thu, 17 Sep 2020 16:23:50 GMT

@alepage wrote:

Hello,

The first data set that I have is like the following one (partial dataset)

Var1 Var2

4426.00 1.00

5644.00 2.00

19569.00 4.00

3362.00 1.00

14427.00 1.00

10643.00 1.00

2490.00 1.00

4471.00 1.00

When I execute the following code

data report.cnts;
set test;
run;

What I have in Excel is:

Var1 Var2

4426 1

5644 2

19569 4

3362 1

14427 1

10643 1

2490 1

4471 1

This is more an Excel issue, I believe.

I have made the same test but with sashelp.class and I am getting the same value in Excel than those into the dataset.

I think that Excel reads the first 200 rows to see the format and as in my dataset all those values have .00 , Excel round up all values.

Regards,

I bet the cells with the missing decimals are now "General" format. Excel would need to have the cells as Numeric with 2 decimals displayed. Which will likely mean using an approach that allows setting TAGATTR options to force the desired Excel appearance like using ODS Excel for output with Proc Print or similar.