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

time precision error

Reply
Occasional Contributor JPB
Occasional Contributor
Posts: 6

time precision error

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?
Occasional Contributor JPB
Occasional Contributor
Posts: 6

Re: time precision error

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?
Occasional Contributor JPB
Occasional Contributor
Posts: 6

Re: time precision error

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?
Super Contributor
Super Contributor
Posts: 3,174

Re: time precision error

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.
Occasional Contributor JPB
Occasional Contributor
Posts: 6

Re: time precision error

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

http://mobile.experts-exchange.com/Q_20074640.html
Contributor
Posts: 27

Re: time precision error

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.
Occasional Contributor JPB
Occasional Contributor
Posts: 6

Re: time precision error

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.
Contributor
Posts: 27

Re: time precision error

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;
Grand Advisor
Posts: 9,699

Re: time precision error

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.
Super Contributor
Posts: 291

Re: time precision error

Friends don't let friends use excel !
Occasional Contributor JPB
Occasional Contributor
Posts: 6

Re: time precision error

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.
Contributor
Posts: 27

Re: time precision error

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.
Contributor
Posts: 27

Re: time precision error

*do NOT share epochal dates
Post a Question
Discussion Stats
  • 12 replies
  • 462 views
  • 0 likes
  • 5 in conversation