Date-time excel variable into SAS date issue

[AnilGaurav]

Hi,

I'm importing a excel file into SAS. While importing, the date time which has 9/1/2020 7:28:11 AM (Date/time format) in Excel gets converted into a Text in SAS environment into 22159.311234.

I have found the solution to convert this original date and time  per below SAS code. However, 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 SAS code suggested.

data want;
  set review.QueryDetail;

 format date date9.;
 format time timeampm.;
 format dt datetime21.;
 qdate = input(querydate,??best.);
 date=int(qdate)-21916;
 time=(qdate-int(qdate))*60*60*24;
 dt=date*24*60*60+time;

run;

The other code suggested here also used but I am getting same issue, it showing as 31AUG1960.

data want;
  set review.QueryDetail;

 format sasdate date9.;

 qdate = input(querydate,??best.);
 Adjusted_Datetime = (qdate + '30dec1899'd) * 86400;
 sasdate = datepart(Adjusted_Datetime);
run;

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

I know there are many suggestion to convert the excel file into csv as excel has lots of issue but unfortunately I cant convert these into csv.

Thanks,

Anil

[ballardw]

If you have a character value then perhaps this will show you a way with a different INFORMAT.

data example;
 x="9/1/2020 7:28:11 AM";
 y= input(x,anydtdtm32.);
 format y datetime18.;
run;

[Kurt_Bremser · Accepted solution]

Looks like the INT value of your qdate is already correct, so you only need to transform the time. Do not subtract the 21916 when you calculate the date.

[AnilGaurav]

Thanks for your quick response, it does works.