Losing significant digit with Libname Temp xlsx ''

[alepage]

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,

[PaigeMiller]

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

[alepage]

format 15.2 so only two significant digits.

[PaigeMiller]

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.

[alepage]

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

[alepage]

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,

[Kurt_Bremser]

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?

[ballardw · Accepted solution]

---

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

[ballardw]

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?