07-22-2015 12:11 PM
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
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?
07-24-2015 08:26 AM
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
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.
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.