Keep date as text when exporting sas dataset to CSV

[chinna0369]

Hi all, 

I am trying export one of my sas data set into CSV file. 

below is the data set:

when I open that CSV file in excel then I am getting the above highlighted value as "5-JAN".

How can keep that value as it is when exporting?

Thank you in advance!

Thanks,

Adithya

[ballardw]

---

@chinna0369 wrote:

Hi all, 

I am trying export one of my sas data set into CSV file. 

below is the data set:

when I open that CSV file in excel then I am getting the above highlighted value as "5-JAN".

How can keep that value as it is when exporting?

Thank you in advance!

Thanks,

Adithya

---

My first instruction would be to not open CSV files in Excel, second would be not to save them from Excel. This is an Excel behavior. Excel will interpret cells to be of certain types based on content. In this case, it has decided the value is a date and one of the Excel preferred displays for such things is the day-Month. If you go the one of those cells and change cell property you will likely see the actual value is 5 Jan 2019.

If you show us how you exported your dataset we might be able to provide some hints if you are going to insist on opening them with Excel.

If you open the CSV in a text viewer such as Notepad, Wordpad or similar programs you will likely see the expected values.

[chinna0369]

Yes, below is the code I am using:

proc export data=export 

           outfile="&PROJROOT.\Analysis\&csvFileName"

           dbms=csv replace;

run;

Ok, then how can I save as CSV file not from excel?

Thanks,

Adithya

[Reeza]

This is not a SAS issue, it's an Excel issue. If you open the CSV with a text editor such as Notepad or NotePad++ you'll see the correct values. Excel is changing the values when it opens the file, by default. DO NOT OPEN CSV's with Excel. 

---

@chinna0369 wrote:

Yes, below is the code I am using:

proc export data=export 

           outfile="&PROJROOT.\Analysis\&csvFileName"

           dbms=csv replace;

run;

Ok, then how can I save as CSV file not from excel?

Thanks,

Adithya

---

[ballardw]

---

@chinna0369 wrote:

Yes, below is the code I am using:

proc export data=export 

           outfile="&PROJROOT.\Analysis\&csvFileName"

           dbms=csv replace;

run;

Ok, then how can I save as CSV file not from excel?

Thanks,

Adithya

---

I have different programs associated with CSV so I can open with the desired program. In Windows from the file explorer right click to bring up the file menu, select Open With. If nothing except Excel is associated then you can click on "Choose another app" to bring up a menu box where you can select another program such as Notepad, Wordpad or such. You can even change the default opening behavior at that time.

After you have done this the next time you want to open a CSV file, use the right click, Open With and select which program you want at this time to open the file.

[Ksharp]

If you have to use Excel open CSV, then add single quote before TRT. Like : trt=cats("'",trt); . and PROC IMPORT the table .

[Tom]

Do not let your computer automatically use Excel to open the CSV file.  Instead open Excel and then use the menu item that lets' you bring in a CSV file.

Here is an excellent article from 12 years ago (updated 7 years ago) that explains what Excel is doing wrong.

https://practicalsurveys.com/technology/excelcsv.php

Excel seems to making this harder and harder with each version of Excel. 

Instructions for Office 365 are here:  https://support.office.com/en-us/article/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857

So you need to ENABLE the wizard that used to be the defaule.

The from the DATA menu select Get Data. Then find the legacy text import wizard.

[Tom · Accepted solution]

I played around with the new From Text/CSV tool in Excel (under the Data) Menu and it seems to work a little better than the older text import method.  

There is a way to EDIT the data types that it has assigned to the columns after you use it to open the CSV file.  From the Edit screen you can click on the TRANSFORM tab and then select columns and change the data type.

So I took a dump of part of SASHELP.CLASS and edited a few cells to introduce some issues.  I put some things that Excel thinks look like dates in the NAME column and the as the header for the WEIGHT column.

Name,Sex,Age,Height,SEP05
Alfred,M,014,69,112.5
DEC05,F,013,56.5,84
Barbara,F,13,65.3,98
10-19,F,14,62.8,102.5
Henry,M,14,63.5,102.5

I then tried opening it with Excel in three ways.  Once just using File Open.

Then using the legacy text import tool. Where I manually told it that the data had headers, that comma as the delimiter and that it should treat the first three columns as TEXT instead of GENERAL.  That worked except for the header for column E.

Then I used the new From Text/CSV tool, without any changes and it didn't recognize that the file had header row. So it treated all of the columns as text, instead of treating the last two as numbers.

But if I used the EDIT button and the TRANSFORM tab I was able to tell it that it should use first row as headers.  That fixed the last two column types, but I had to manually tell it to treat the third column as text.