Read a date from Excel File

[boas3ad]

How to read date from Excel File

I am importing data from excel file, but I was unable to let SAS read the Date column.

Can anyone kindly help me to solve this issue?

[mohamed_zaki]

Could you give example of your data in the excel. how the date cell data look?

And what the output you are getting now?

[boas3ad]

Thank you for your replay

It is a time series data for stocks returns for different markets. the first column is dates in format mm/dd/yyyy.

I am doing an event study, so I need to use the dates.

[PGStats]

What gets imported into SAS? Make sure your Excel dates are real dates (numbers with a date format) and not character strings.

[udo_sas · Accepted solution]

Hello -

This demo showcases how to import Excel data to SAS using SAS Enterprise Guide: https://www.youtube.com/watch?v=LHmKkkwMXpU

As pointed out already you will have to deal with some format conversion most likely. If you can share how you are reading data from Excel or ideally provide an example of your spreadsheet, we might be able to provide further advise.

Thanks,

Udo

[damanaulakh88]

Hi boas3ad,

If the date has been entered on the Excel spreadsheet as a character string, you need to specify in the input statement as a character string, then convert to date as follows:

labdt = input(lab_date,date9.); format labdt date9.;

Also note that Excel and SAS having different reference dates (i.e. Day 0 in SAS is 1 January 1960 and Day 0 in Excel is 1 January 1900), Use below formulaes to get the date time correct.

SAS_date = Excel_date - 21916;

• SAS_time = Excel_time * 86400;
• SAS_date_time = (Excel_date_time - 21916) * 86400;

Thanks,

Daman