BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
minfante
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

3 REPLIES 3
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Kurt_Bremser
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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