topic Re: Time format changing when export to CSV in SAS Programming

Time format changing when export to CSV

BStenta — Mon, 26 Sep 2016 15:42:35 GMT

Hello,

In my raw data I am using seconds, and I need to format it into mmss. I used hms(0,0,<variable>) in my code, and then used format mmss. This worked properly in my SAS code/table; however, when I export the SAS table to a CSV file, some of the times are being formatted incorrectly (the large times). All of the data is correct in my SAS table, but for some reason when I open the CSV in excel, some of it is no longer accurate.

Does anyone know why this might be occurring? I have attached a picture with two samples.

Thanks,

Bri

Re: Time format changing when export to CSV

ballardw — Mon, 26 Sep 2016 15:53:21 GMT

Your answer is here:

when I open the CSV in excel, some of it is no longer accurate

Excel makes assumptions about values when opening CSV and has been known to change things considerably. Loot at the value in the CSV using a Text program like Notepad or possibly Wordpad and you will see things basically the same as SAS.(unless you have SAVED the file with Excel. In which case re-export the values)

You can see what display format Excel has assigned to the cell an possibly select one that appears as needed.

Re: Time format changing when export to CSV

BStenta — Mon, 26 Sep 2016 15:59:29 GMT

Thank you! You are right. When I open this file in Notepad, the formatting is correct.

Excel is using h:mm for the correct cells, and [h]:mm:ss for the incorrect cells. Is there a way to alter my SAS code so that this does not happen?? I need all the cells to be in the h:mm format.

Thanks.

Re: Time format changing when export to CSV

ballardw — Mon, 26 Sep 2016 16:19:03 GMT

Proc export is going to give you much control.

If you have SAS 9.4 the EXCEL libname may help.

Or ODS tagsets.excelxp can create XML data that Excel can read. With proc print you can provide style appearance overrides and the TAGATTR option on specific variables to control how Excel interpret things.

I seldom get into that as I avoid Excel output as much as possible.