topic Re: time precision error in Microsoft Integration with SAS

time precision error

JPB — Fri, 03 Jun 2011 14:01:54 GMT

I have the following data in Excel in a single column:

Times
8:00
8:30
9:00
9:30
10:00
10:30

I import the sheet into SAS as work.test using the ImportWizard, then try

data example;
set test; where Times=36000;

and the data set "example" has no rows. If I try

data example;
set test; where Times=28800;

the data set "example" has 1 row. If I try

data example;
set test; where Times<= 36000;

the data set "example" has

Times
8:00
8:30
9:00
9:30

but not 10:00. If I try

data example;
set test; where Times<= 36000.0000000001;

then 10:00 is included. If I try

data example;
set test; where Times<= 36000.00000000001;

then 10:00 is excluded. Any ideas?

Re: time precision error

JPB — Fri, 03 Jun 2011 14:03:01 GMT

The rest of my post... If I try

data example;
set test; where Times<= 36000;

the data set "example" has

Times
8:00
8:30
9:00
9:30

but not 10:00. If I try

data example;
set test; where Times<= 36000.0000000001;

then 10:00 is included. If I try

data example;
set test; where Times<= 36000.00000000001;

then 10:00 is excluded. Any ideas?

Re: time precision error

JPB — Fri, 03 Jun 2011 14:06:34 GMT

Let's try again... If I try

data example;
set test; where Times<= 36000;

the data set "example" has

Times
8:00
8:30
9:00
9:30

but not 10:00. If I try

data example;
set test; where Times<= 36000.0000000001;

then 10:00 is included. If I try

data example;
set test; where Times<= 36000.00000000001;

then 10:00 is excluded. Any ideas?

Re: time precision error

sbb — Fri, 03 Jun 2011 14:30:50 GMT

Recommend the OP read (and bookmark for future reference) this post about the SAS forums text-posting mark-up rules.

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.

Re: time precision error

JPB — Fri, 03 Jun 2011 15:06:13 GMT

I think I found the problem; it is a problem with Excel's precision:

http://mobile.experts-exchange.com/Q_20074640.html

Re: time precision error

AlanC — Fri, 03 Jun 2011 15:33:41 GMT

See this reference on how Excel handles dates and times:

http://www.cpearson.com/excel/datetime.htm

The issue could also be how you are bringing in the Excel data which is not specified in your post. You also don't specify which version of Excel. The post you referred to was from 2001 and a lot of things have changed with Excel since then.

Can you provide more clarification?

I am having issues with my SAS/Excel engine so I have to get that corrected to do the testing but more info can help once I am to that point.

Re: time precision error

JPB — Fri, 03 Jun 2011 15:48:59 GMT

Thanks for the reference.

I am using the dialog that appears when you click "File->Import Data..." with default settings, which is what I meant when I said I am using the ImportWizard.

I am using Excel 2007. I also tried Excel 2003.

Re: time precision error

AlanC — Fri, 03 Jun 2011 16:49:25 GMT

I just got SAS working on Excel. I used Excel 2010, and wrote 1 column called TIME with half-hour increments from 08:00 - 22:00. I then used the Import Wizard using PC Files Server, unchecked the Use DATE. format for Date/Time columns, and successfully imported the data. All times look correct.

Code:

ROC IMPORT OUT= WORK.TEST
DATAFILE= "x:\temp\test2.xlsx"
DBMS=EXCELCS REPLACE;
SERVER="ALAN-PC";
PORT=8621;
SSPI=YES;
RANGE="Sheet1$";
SCANTEXT=YES;
USEDATE=NO;
SCANTIME=YES;
RUN;

Re: time precision error

ballardw — Fri, 03 Jun 2011 17:29:10 GMT

My first approach with any data that originates in Excel is to turn of the format in the source sheet and look at the values. Excel does so much rounding and such that exact matches such as your =36000 can be quite problematic on top of the precision issue.

Re: time precision error

Bill — Fri, 03 Jun 2011 17:35:22 GMT

Friends don't let friends use excel !

Re: time precision error

JPB — Fri, 03 Jun 2011 17:53:45 GMT

It *looks* correct on mine as well. Now try something like

data example;
set test; where Times=36000;

and see what happens. You should get a data set with 1 row, but when I use Excel 2003/7 and SAS, I get 0 rows.

Re: time precision error

AlanC — Fri, 03 Jun 2011 20:50:53 GMT

I still cannot replicate it. SAS appends a date to the time to make a datetime on the import. Hence, it appends date 0 in Excel.

I would suggest just taking the int portion of the value and using it. Something could be happening with the translation from Excel to SAS, with SAS, with Excel, etc.

You are ultimately doing an equality clause usingt the numeric value since you know how SAS handles its epochal time values. I would instead use a function to identify the actual time value and use that in the where clause, especially when dealing with 2 systems that do share epochal dates.

Re: time precision error

AlanC — Fri, 03 Jun 2011 20:51:32 GMT

*do NOT share epochal dates