Date time excel to SAS

[yapalparvi]

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

[ballardw]

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.

[yapalparvi]

Thanks for your reply.

I had used the following

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

[ballardw]

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.

[art297]

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

[yapalparvi]

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.

[art297]

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

[AnilGaurav]

Hello HTH,

I have same issue, the date format is 9/1/2020 7:28:11 AM in excel is showing as 22159.311234 in SAS. I am using the same code to convert the excel date time to SAS suggested by you,  but unfortunately I am not getting date incorrect but time is showing correct.

The date should be 01SEP2020 however it showing as 31AUG1960.

The time is showing correctly as  7:28:11 AM using the below SAS code, Could you please advise.

data want;
  set review.QueryDetail;

 format date date9.;
 format time timeampm.;
 format dt datetime21.;
 date=int(querydate)-21916;
 time=(querydate-int(querydate))*60*60*24;
 dt=date*24*60*60+time;
run;