topic Exporting milliseconds? in Microsoft Integration with SAS

Exporting milliseconds?

minfante — Fri, 20 Feb 2015 00:15:21 GMT

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!

Re: Exporting milliseconds?

ChrisHemedinger — Fri, 20 Feb 2015 01:13:25 GMT

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

Re: Exporting milliseconds?

Kurt_Bremser — Fri, 20 Feb 2015 08:05:29 GMT

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.

Re: Exporting milliseconds?

minfante — Fri, 20 Feb 2015 16:54:42 GMT

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...