topic Re: ODS Excel + Proc Report - issue with Datetime22.3 values in SAS Programming

ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Fri, 27 Jul 2018 20:40:11 GMT

Brief description of the issue:

original value 22Feb2018:22:04:01.485 is changed to 22Feb2018:22:04:01.430 when sas dataset is exported as .xlsx using ODS excel and Proc Report combination.

The last 3 digits are changing automatically in the excel export. This happens with proc export with dbms=xlsx.

But dbms=csv output seems to maintain the values correctly.

I have tried other formats but they do not work. ODS excel is fine otherwise for all formats.

Seems like this needs to be addressed within SAS software per my knowledge.

Appreciate any feedback and discussions.

Kind Regards.

Addl Information/Steps:

1. Here is the code:

ods listing close;
options nobyline nolabel;
ods escapechar='^';
ods excel file = "&&xlout.&&mem&i...xlsx" style = pearl options (sheet_interval="none" sheet_name="Data");

proc report split='~' nocenter missing list;
    column _all_;
run;

ods excel close;
ods listing;

2. Here are the original data set values

3. Here is the incorrect .xlsx file output

4. Here is an image of .csv output

5. The define statement from log file is as below.

DEFINE createdt / SUM FORMAT= DATETIME22.3 WIDTH=22 SPACING=2 RIGHT "createdt" ;
DEFINE Updated / SUM FORMAT= DATETIME22.3 WIDTH=22 SPACING=2 RIGHT "Updated" ;

Re: ODS Excel + Proc Report - issue with Datetime22.3 values [how to improve your question]

Community_Guide — Fri, 27 Jul 2018 19:50:20 GMT

Hello @rkval,

Your question requires more details before experts can help. Can you revise your question to include more information?

Review this checklist:

Specify a meaningful subject line for your topic. Avoid generic subjects like "need help," "SAS query," or "urgent."
When appropriate, provide sample data in text or DATA step format. See this article for one method you can use.
If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @ballardw

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

ballardw — Fri, 27 Jul 2018 19:51:41 GMT

What does the excel value look like when you remove the cell display format? or change to just a number with a few decimal values and no date format?

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

Reeza — Fri, 27 Jul 2018 20:07:50 GMT

Post your full code used and your SAS version.

Some code that we can run to test the issue is helpful.

@rkval wrote:

Brief description of the issue:

original value 22Feb2018:22:04:01.485 is changed to 22Feb2018:22:04:01.430 when sas dataset is exported as .xlsx using ODS excel and Proc Report combination.

The last 3 digits are changing automatically in the excel export. This happens with proc export with dbms=xlsx.

But dbms=csv output seems to maintain the values correctly.

I have tried other formats but they do not work. ODS excel is fine otherwise for all formats.

Seems like this needs to be addressed within SAS software per my knowledge.

Appreciate any feedback and discussions.

Kind Regards.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

Reeza — Fri, 27 Jul 2018 20:30:13 GMT

If you export as character this issue doesn't appear but not a nice workaround.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Fri, 27 Jul 2018 20:47:16 GMT

Hi,

When changed to number with 3 decimals it displays a completely new value. I want a date time value with hours, minutes, seconds and milliseconds retained as is.

I'm fine with exporting the field as text but posted this so it may be addressed.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Fri, 27 Jul 2018 20:53:22 GMT

Hi,

Thanks for the response. I'm fine with exporting as text.

Just posted the issue so it can be addressed. I was curious as to why the last three digits were being changed. For same date/time upto seconds the last three digits are changed to a same value. Example: For all rows with 13APR2018:19:14:33:--- with the last '---' being milliseconds have same value in the exported file whereas dataset has different values.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Fri, 27 Jul 2018 20:56:30 GMT

Added additional information to the original post.

Thanks again.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

Reeza — Fri, 27 Jul 2018 21:01:56 GMT

You can try and add a TAGATTR statement to the PROC REPORT to force the format but I suspect you'll have the same issue.

http://support.sas.com/kb/38/143.html

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Fri, 27 Jul 2018 21:57:58 GMT

The tag attributes is not working. Found another thread discussing the issue with date time values. Could be the solution for now.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Write-DATETIME-values-to-ODS-EXCEL/td-p/205533

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

ballardw — Fri, 27 Jul 2018 22:16:00 GMT

I suspect a big part of this is related to how the two programs store datetime information.

SAS stores times and datetime values as seconds. The only decimal portion reflects portions of a second.

EXCEL however stores everything as DAYS. And ALL of the time information is stored as a fraction of a day. So I belive the difference is coming partially as to the available precision and number of bytes available for storage.

You said the CSV appeared to look correct. Did you import that into Excel and examine the value? And compare the numeric value from the CSV import with the ODS Excel created value using NUMERIC not datetime Excel formats (change cell properties to Numeric with about 10 decimals.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

Tom — Fri, 27 Jul 2018 22:27:48 GMT

You are probably just hitting some limits on precision of the numbers that are transferred.

The difference between your two values is occurring in the 12 and 13 significant digits of the underlying numbers.

318   data test ;
319     value1 = '22Feb2018:22:04:01.485'dt;
320     value2 = '22Feb2018:22:04:01.430'dt;
321     diff = value1-value2 ;
322     sdate1 = datepart(value1);
323     sdate2 = datepart(value2);
324     stime1 = timepart(value1);
325     stime2 = timepart(value2);
326     edate1 = sdate1-'30DEC1899'd + stime1/'24:00't;
327     edate2 = sdate2-'30DEC1899'd + stime2/'24:00't;
328     ediff = edate1-edate2 ;
329     put (_all_) (=comma32.8 /);
330   run;

value1=1,834,956,241.48500000
value2=1,834,956,241.43000000
diff=0.05499983
sdate1=21,237.00000000
sdate2=21,237.00000000
stime1=79,441.48499990
stime2=79,441.43000007
edate1=43,153.91946163
edate2=43,153.91946100
ediff=0.00000064

You might get more exact data transfer if you converted your SAS datetime values from number of seconds to number of milliseconds (multiple by 1000). That way you could transfer an integer value instead of a floating point value. You could then try to convert back to a datetime value in Excel. Or just remove the milliseconds and store them as a separate integer.

Or possibly if you split your DATETIME field into separate DATE and TIME values. Then there are more bits available for storing the fractional seconds since it is tacked onto the end of a 5 digit integer instead of a 10 digit integer.

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

Reeza — Fri, 27 Jul 2018 22:27:17 GMT

That's from 2015 and there have been several major releases/updates since then. May be worth following up with SAS Tech Support.

@rkval wrote:

The tag attributes is not working. Found another thread discussing the issue with date time values. Could be the solution for now.

https://communities.sas.com/t5/ODS-and-Base-Reporting/Write-DATETIME-values-to-ODS-EXCEL/td-p/205533

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Sat, 28 Jul 2018 00:01:13 GMT

I'm not clear how we can make that comparison after the export to .csv. The values are in 'General' pretty much text format and cannot be changed to number to compare with output from .xlsx correct?

Re: ODS Excel + Proc Report - issue with Datetime22.3 values

rkval — Sat, 28 Jul 2018 00:07:59 GMT

Thank you for the response. A warning in the log would be helpful for these limitations. For the time being I'm okay with using the text values.