topic CSV output in SAS Programming

CSV output

ursula — Wed, 08 Jul 2015 20:22:55 GMT

Hi,

I have a problem with output CSV data.

I have a file consist of Month/Year in Text format, for example 11/2007, 01/2003 ....ect.

when I use ODS csv to output the file, and then open in excel file, the Month/Year values change from 11/2007 to 7-Nov, and 01/2003 changes to 3-Jan.

I wonder, is there a way to hold the original date value as is when I do ODS csv and looks the same when open in excel file.

Any idea would be appreciate.

Thank you.

Re: CSV output

Peter_C — Wed, 08 Jul 2015 20:47:38 GMT

Problem is the import in excel

Re: CSV output

ursula — Wed, 08 Jul 2015 21:19:29 GMT

Thank you, Peter.

I noticed, this is happened only if the date value is Month/year, but it works fine if the date value is Month/Day/Year.

Re: CSV output

Eric_stats — Wed, 08 Jul 2015 22:37:27 GMT

Hi, Ursula. A couple of suggestions for possibilities.

1. In Excel, you could format the column via Format Cells, Custom, mm/yyyy

2. When outputting to .csv from SAS, can you add a ' in front of each date record, as in '01/2001. Should enable it to be viewed "as-is" in excel.

Re: CSV output

ursula — Thu, 09 Jul 2015 15:49:44 GMT

Thanks Eric for the suggestion.

The date record in my file actually is a text format, I have no idea why SAS read as numeric and interpret differently (wrong) when output as CSV file.

Re: CSV output

Erico — Thu, 09 Jul 2015 16:49:36 GMT

Hi Ursula,

As others have pointed out, this is for more likely an Excel problem than a SAS problem.

I suggest you look at the csv file in a plain text editor such as Notepad, not Excel, to see exactly what SAS is putting out.

In Excel (at least on my computer), if I enter 11/2007 in a cell, Excel automatically converts it to the date value for November 1, 2007, and displays Nov-07. As Eric@Stats suggests, you can format this with the custom format mm/yyyy so that it displays as 11/2007, but the internal value is still the date value for 11/1/2007, so if you later transfer the data to another program or another format, you may not get what you want. Essentially, Excel corrupts the data when it imports it.

Eric

Re: CSV output

ursula — Thu, 09 Jul 2015 17:40:38 GMT

Thank you Erico and Eric.

I have 2 versions output the file into csv, the first output shows the date as 7-Nov, and the second output is Nov-07, I don't know why it came out different output with the same procedure.

I have tried to do custom format the date, it looks good for Nov-07 (format mm/yyyy -- 11/2007), but not the 7-Nov. I tried different format for 7-Nov, but it came out year as 2015.

Re: CSV output

DanielMK — Thu, 09 Jul 2015 18:16:43 GMT

Hi Ursula,

When a CSV file is opened in Excel, Excel tries to guess the data type of each cell. If the cell only contains numbers and some symbols (e.g., /) it will interpret it as a date. This is regardless of the data type of your original SAS dataset. Excel, however, does not support incomplete dates so it also tries to complete your dates, and sometimes it does it right and sometimes not. On top of that, once the date is complete, Excel applies a format to the cell to display the date in a certain way. You can't change how Excel treats dates, so depending on what your end goal is, I would suggest one of the following two options:

1-In your SAS dataset, i.e., before exporting as CSV, add an apostrophe/single quote (') before the incomplete date, and then make sure this symbol appears in the CSV file. When Excel opens the CSV file, it will interpret the cell as text and will show exactly what you want. However, you won't be able to do calculations or sorting in Excel with this field.

2-In your SAS dataset, complete the date (e.g., '03/2015' should be '03/01/2015' or '01-Mar-2015') and then convert it to a date field with the input function. Excel will consistently import a complete date correctly.

I hope this helps,

Daniel

Re: CSV output

ursula — Thu, 09 Jul 2015 19:52:56 GMT

Thanks to all of you who are responding to my question.

SAS communities would not be success without your support!

Have a great day!