topic Re: importing columns with hours from Excel in SAS Procedures

importing columns with hours from Excel

lalohg — Wed, 22 Jul 2015 16:11:05 GMT

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.

Re: importing columns with hours from Excel

ChrisNZ — Fri, 24 Jul 2015 02:52:54 GMT

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?

Re: importing columns with hours from Excel

anan — Fri, 24 Jul 2015 07:22:09 GMT

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

Re: importing columns with hours from Excel

MadhuKorni — Fri, 24 Jul 2015 08:09:08 GMT

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.

Re: importing columns with hours from Excel

lalohg — Fri, 24 Jul 2015 12:26:19 GMT

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

Re: importing columns with hours from Excel

ballardw — Fri, 24 Jul 2015 15:42:20 GMT

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.