Help using Base SAS procedures

importing columns with hours from Excel

Posts: 54

importing columns with hours from Excel

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



Posts: 2,350

Re: importing columns with hours from Excel

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?

Posts: 54

Re: importing columns with hours from Excel

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

Super User
Posts: 13,531

Re: importing columns with hours from Excel

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.

Occasional Contributor
Posts: 5

Re: importing columns with hours from Excel

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

Frequent Contributor
Posts: 77

Re: importing columns with hours from Excel

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.

Ask a Question
Discussion stats
  • 5 replies
  • 5 in conversation