time precision error

[JPB]

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?

[JPB]

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?

[JPB]

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?

[sbb]

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.

[JPB]

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

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

[AlanC]

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.

[JPB]

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.

[AlanC]

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;

[ballardw]

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.

[Bill]

Friends don't let friends use excel !

[JPB]

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.

[AlanC]

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.

[AlanC]

*do NOT share epochal dates