importing columns with hours from Excel

[lalohg]

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.

[ChrisNZ]

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?

[lalohg]

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

[ballardw]

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.

[anan]

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

[MadhuKorni]

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.