SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Exporting milliseconds?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Exporting milliseconds?

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!


Accepted Solutions
Solution
‎02-19-2015 08:13 PM
Community Manager
Posts: 2,888

Re: Exporting milliseconds?

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

View solution in original post


All Replies
Solution
‎02-19-2015 08:13 PM
Community Manager
Posts: 2,888

Re: Exporting milliseconds?

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

Super User
Posts: 7,416

Re: Exporting milliseconds?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Exporting milliseconds?

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1136 views
  • 3 likes
  • 3 in conversation