topic Re: Read a date from Excel File in SAS Forecasting and Econometrics

Read a date from Excel File

boas3ad — Sat, 13 Sep 2014 00:01:47 GMT

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?

Re: Read a date from Excel File

mohamed_zaki — Sat, 13 Sep 2014 00:11:24 GMT

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

And what the output you are getting now?

Re: Read a date from Excel File

boas3ad — Sat, 13 Sep 2014 00:37:44 GMT

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.

Re: Read a date from Excel File

PGStats — Sat, 13 Sep 2014 00:42:41 GMT

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

Re: Read a date from Excel File

udo_sas — Mon, 15 Sep 2014 12:47:01 GMT

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

Re: Read a date from Excel File

damanaulakh88 — Mon, 15 Sep 2014 13:03:31 GMT

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