DATA Step, Macro, Functions and more

Date time excel to SAS

Reply
Occasional Contributor
Posts: 6

Date time excel to SAS

Hi,

 

I'm importing a excel file into SAS. While importing, the date time which was 6/20/2016  8:14:08 PM (Date/time format) in Excel gets converted into a Text in SAS environment into 42541.843148148.

 

Could you please let me know how to get back the original date and time in date/time format in SAS

 

Thanks,

R

 

 

 

Super User
Posts: 10,527

Re: Date time excel to SAS

HOW did you import the data into SAS?

And use the instruction here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to make a portion of the SAS data set into datastep code you can paste here to show us what the resulting data really looks like.

I doubt that SAS created a character variable of that appearance though it looks very likely to be a RAW excel datetime value. Go to excel and format one of the cells with the datetime to numeric with about 10 decimal points and see if that looks the same.

Occasional Contributor
Posts: 6

Re: Date time excel to SAS

Thanks for your reply.

 

I had used the following

 

proc import out=work.test  datafile="C:\Data\filename"
dbms=xlsx replace;
sheet="Sheet1";
run;

Super User
Posts: 10,527

Re: Date time excel to SAS

I really don't trust Proc Import very much as it has to GUESS about too many things in your data.

One thing you may try is to open the spreadsheet highlight the entire colum and format all cells to the same datetime appearance.

HOWEVER, since the nature of spreadsheets means that people may have entered dates mixed with date times you may have to post process things to force the conversion of Excel date and datetime values to SAS date times. That is ugly enough see if the first suggestion helps. If the first rows are blank and not formatted then SAS is told by the Excel engine the the column is character and then SAS treats everything as character in that column. You might want to make sure one of the values in in the top row if the order of the Excel file isn't critical for later processing.

PROC Star
Posts: 7,363

Re: Date time excel to SAS

[ Edited ]

You can easily convert those datetimes into the dates and times they actually represent. Of course, they could also be easily converted to represent SAS datetimes:

 

data have;
input exl_dt;
format date date9.;
format time timeampm.;
format dt datetime21.;
date=int(exl_dt)-21916;
time=(exl_dt-int(exl_dt))*60*60*24;
dt=date*24*60*60+time;
cards;
42541.843148148
;

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 6

Re: Date time excel to SAS

Helo Art,

 

Thanks for your reply. It works, but how do I make all the entries of the column work for eg., say ObservedTime label in the imported excel file.

 

 

PROC Star
Posts: 7,363

Re: Date time excel to SAS

 

data have;
  input ObservedTime;
cards;
42541.843148148
;

data want (drop=_:);
  set have (rename=(ObservedTime=_ObservedTime));
  format date date9.;
  format time timeampm.;
  format ObservedTime datetime21.;
  date=int(_ObservedTime)-21916;
  time=(_ObservedTime-int(_ObservedTime))*60*60*24;
  ObservedTime=date*24*60*60+time;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 6 replies
  • 145 views
  • 0 likes
  • 3 in conversation