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

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- importing columns with hours from Excel

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

07-22-2015 12:11 PM

Hello there,

I have an Excel book and would like to import all sheets from SAS. All sheets have a common ID and Name of people but with different length. Does anyone know how to import all sheets that I need without importing each sheet at the time?

Also, When I tried to import only one sheet with columns having "hour at start" and "hour at end" of a specific surgical procedure I get years in SAS instead of hours. I have changed the format of these columns in Excel to "text", "general" and "time" but it didn't work

all your help will be appreciated

Thanks

Eduardo.

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

07-23-2015 10:52 PM

1- You can only import one sheet a a time.

2- What exact values do you get? You are not getting years, you are getting numbers. What examples of these numbers can you provide?

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

07-24-2015 08:26 AM

Thanks Chris,

You are right I don't get years I get dates 29DEC1899 or mostly 30DEC1899 for all entries.

Hour columns in Excel range from 0:00:00 to 23:59:00

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

07-24-2015 11:42 AM

I believe that 30Dec1899 is how SAS receives an Excel date of 0 (which Excel in Microsoft,s infinite will display as January 0, 1900). Since the algorithm works for Excel 1/1/1900 (value of 1) then subtract one day from 1/1/1900 you get 12/30/1899. Except Excel doesn't really use dates prior to 1/1/1900.

Test this by typing a 0 into a cell in Excel then format as a date.

also Type a date prior to 1/1/1900 and then try to convert to a basic number.

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

07-24-2015 03:22 AM

Hi @lalohg can you show us a sample data. and the output you want.

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

07-24-2015 04:09 AM

By using Libname we can bring all the spreadsheets in excel workbook to SAS environment in the form of datasets.

The spreadsheets names will be the Dataset names followed by $.

For example if Sample.xls is the workbook that consists of Acc, AccNums, CustIds as spreadsheets then if you use libname statement thenAcc$, AccNums$, CustIds$ will be the dataset names.

**Libname Libref Excel "Path\Sample.xls";**

Libref is a user defined name.