Exporting milliseconds?

[minfante]

Hi,

Wondering if anyone has any ideas for the following issue: I seem to be having trouble exporting milliseconds from datetime or time variables. A time variable in SAS of 11:00:06.888, for example, becomes 11:00:07.000 in Excel after a proc export.

The only workaround I've been able to find is to create a new variable which contains only the seconds (i.e. 6.888), and then combining that with the rest of the time variable in Excel. 

Thanks!

[ChrisHemedinger · Accepted solution]

Try applying the proper SAS format, and then export to CSV instead of directly into Excel format:

data a;
dt = datetime();
time = timepart(dt);
seconds = second(time);
format dt datetime22.3 time time12.3 seconds 8.3;
run;

proc export data=a outfile="c:\temp\dt.xlsx"
  dbms=xlsx replace;
run;

proc export data=a outfile="c:\temp\dt.csv"
  dbms=csv replace;
run;

Output CSV:

dt,time,seconds

19FEB2015:20:08:36.716,20:08:36.716,36.716

I found that CSV retains the precision, while sometimes the Excel format loses something in translation.  But others might have a good workaround for that.

Chris

[Kurt_Bremser]

The direct interface to MS Office uses a set of intermediary routines provided by Microsoft. As with most software from this company, these are unstable, sloppily designed and coded, and change their behaviour from release to release. That's why I do everything to avoid touching this interface at all.

.CSW works. And works. And works. Everytime.

[minfante]

Thanks, Chris!  This was helpful, and worked well.

I am not a huge fan of CSV files because I tend to use multiple sheets, though I guess I'd be willing to give that up to avoid this problem...