09-26-2016 11:42 AM
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.
09-26-2016 11:53 AM
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.
09-26-2016 11:59 AM
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.
09-26-2016 12:19 PM
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.