BookmarkSubscribeRSS Feed
JPB
Calcite | Level 5 JPB
Calcite | Level 5
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?
12 REPLIES 12
JPB
Calcite | Level 5 JPB
Calcite | Level 5
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
Calcite | Level 5 JPB
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5 JPB
Calcite | Level 5
I think I found the problem; it is a problem with Excel's precision:

http://mobile.experts-exchange.com/Q_20074640.html
AlanC
Barite | Level 11
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.
https://github.com/savian-net
JPB
Calcite | Level 5 JPB
Calcite | Level 5
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
Barite | Level 11
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;
https://github.com/savian-net
ballardw
Super User
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
Quartz | Level 8
Friends don't let friends use excel !
JPB
Calcite | Level 5 JPB
Calcite | Level 5
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
Barite | Level 11
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.
https://github.com/savian-net
AlanC
Barite | Level 11
*do NOT share epochal dates
https://github.com/savian-net

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
  • 12 replies
  • 1926 views
  • 0 likes
  • 5 in conversation