Importing an Excel spreadsheet into SAS.

[hein68]

I'm trying to import an Excel spreadsheet into SAS.  I have data like this:

ID        Date                Time             VM

44       8/28/2015         0:00:00         1691.4

44       8/28/2015         0:01:00         3001.51

I need the data in SAS to be:

ID: numeric

Date: Character $30

Time: Character $30

VM: numeric

When I import in the data, the Date and Time variables have all missing values, and I don't know why. 

[ballardw · Accepted solution]

---

@hein68 wrote:

I'm trying to import an Excel spreadsheet into SAS.  I have data like this:

ID        Date                Time             VM

44       8/28/2015         0:00:00         1691.4

44       8/28/2015         0:01:00         3001.51

I need the data in SAS to be:

ID: numeric

Date: Character $30

Time: Character $30

VM: numeric

When I import in the data, the Date and Time variables have all missing values, and I don't know why. 

---

Show the exact code you ran.

Or describe any wizards you used.

My first guess is that the value in excel for date/time is a single cell, is numeric and the approach you used didn't match the data.

Why do you want DATE to be 30 characters long if the longest value is likely to be 10?

You may have to start by using Excel and do a file save as to CSV format to read/import as there can be more control involved.

[hein68]

Thank you. Your comment about saving the Excel file as a CSV helped me to figure it out. They had saved the Excel file in an odd format. Once I fixed that it imported in fine.

[singhsahab]

Hi,

If you will import an Excel sheet , date value will be character by default and time would be numeric. Post Import you can change the length of date variable by using Length statement and put function can be use to create new variable by time as character type. 

Below is a sample code :

PROC IMPORT DATAFILE="PATH\bOOK1.XLS" 
OUT=HAVE DBMS=EXCEL REPLACE;
RUN;


DATA WANT (RENAME=(TIME1=TIME) DROP=TIME);
LENGTH DATE $30.;
SET HAVE;
TIME1=PUT(TIME,30.);
RUN;

Regards...

🙂