Time format changing when export to CSV

[BStenta]

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 

[ballardw]

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.

[BStenta]

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. 

[ballardw]

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.