topic Problem reading correct format in Excel after using ODS? in ODS and Base Reporting

Problem reading correct format in Excel after using ODS?

ybz12003 — Fri, 08 Apr 2016 15:51:25 GMT

Hi, all:

I exported CSV file by using ODS. However, Excel automaticlly remove ".0" and become integer. I use the notepad to open CSV file, the numbe format are correct as ".0". How to fix this problem?

data nhssfinal;
set yt.nhssfinal (keep= type formatted_value);
run;
ods csv file="E:\My SAS Files\NHSS2020.CSV";
style=minimal;
options missing="";
proc print data=nhssfinal;
run;
ods csv close;

Re: Problem reading correct format in Excel after using ODS?

ybz12003 — Fri, 08 Apr 2016 15:56:30 GMT

Below are some example showned in CSV.

15	O	17
16	O	8
17	O	4.1
18	O	5.6
19	O	9.1
20	O	27.1
21	O	3.8
22	O	30.8
23	O	15.4
24	O	8.6
25	O	6.2
26	O	16.5
27	O	9.3
28	O	2.3
29	O	4.4
30	O	13.6
31	O	4
32	O	20.6
33	O	5.9
34	O	21.3
35	O	15.1
36	O	1.6
37	O	9.8
38	O	8.2
39	O	6.8
40	O	11.7
41	O	8
42	O	16.2
43	O	3.5
44	O	14.5
45	O	18
46	O	4
47	O	1
48	O	12.3
49	O	7.6
50	O	4.6
51	O	4.2
52	O	1.4
53	N	47989
54	N	680
55	N	31
56	N	681
57	N	187

Re: Problem reading correct format in Excel after using ODS?

ybz12003 — Fri, 08 Apr 2016 15:59:15 GMT

Due to CSV couldn't be uploaded from here. I copy some error from I read in CSV format. Please check the row 15,16,31,41,46,47.

Thanks.

Re: Problem reading correct format in Excel after using ODS?

ballardw — Fri, 08 Apr 2016 16:11:19 GMT

Name your CSV file with a TXT extension and it can be uploaded without problems.

If your CSV file has values with decimals like 5.0 and Excel removes them, that is an Excel issue.

Is your question related to seeing the deicimals in SAS?

Post how you read or import the CSV file.

Re: Problem reading correct format in Excel after using ODS?

Ksharp — Sat, 09 Apr 2016 04:17:39 GMT

Add a TAB ( '09'x ) character or a single quote( ' ) before that numeric variable .