Forecasting using SAS Forecast Server, SAS/ETS, and more

Read a date from Excel File

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Read a date from Excel File

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?


Accepted Solutions
Solution
2 weeks ago
SAS Employee
Posts: 416

Re: Read a date from Excel File

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

View solution in original post


All Replies
Super Contributor
Posts: 490

Re: Read a date from Excel File

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

And what the output you are getting now?

New Contributor
Posts: 3

Re: Read a date from Excel File

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.

Respected Advisor
Posts: 4,651

Re: Read a date from Excel File

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

PG
Solution
2 weeks ago
SAS Employee
Posts: 416

Re: Read a date from Excel File

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

Frequent Contributor
Posts: 81

Re: Read a date from Excel File

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1612 views
  • 0 likes
  • 5 in conversation