Date-time excel variable into SAS date issue

AnilGaurav — Thu, 01 Oct 2020 15:17:05 GMT

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

Re: Date-time excel variable into SAS date issue

ballardw — Thu, 01 Oct 2020 15:30:02 GMT

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;

Re: Date-time excel variable into SAS date issue

Kurt_Bremser — Thu, 01 Oct 2020 15:54:36 GMT

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.

Re: Date-time excel variable into SAS date issue

AnilGaurav — Thu, 01 Oct 2020 17:39:39 GMT

Thanks for your quick response, it does works.

topic Date-time excel variable into SAS date issue in SAS Programming

Date-time excel variable into SAS date issue

Re: Date-time excel variable into SAS date issue

Re: Date-time excel variable into SAS date issue

Re: Date-time excel variable into SAS date issue