turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 10:01 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 10:03 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 10:06 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 10:30 AM

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.

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

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 11:06 AM

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

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 11:33 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 11:48 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 12:49 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 01:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 01:35 PM

Friends don't let friends use excel !

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 01:53 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 04:50 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-03-2011 04:51 PM

*do NOT share epochal dates