BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
boas3ad
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
udo_sas
SAS Employee

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

5 REPLIES 5
mohamed_zaki
Barite | Level 11

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
Calcite | Level 5

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
Opal | Level 21

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

PG
udo_sas
SAS Employee

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

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