topic Re: correct date after xlsx import in SAS Programming

correct date after xlsx import

CHL0320 — Wed, 27 Feb 2019 22:19:52 GMT

Hi,

I have used proc import a time variable from Xlsx and it turns into a character variable.

For example

In xlsx

Occ_Time

9/22/2016 9:00

9/22/2016 12:00

After proc import. The variable becomes Character and the value are

Occ_Time

42635.375
42635.375
42635.375
42635.375
42635.5

I need to learn how to correct it in my SAS data set.

Thank you.

VDD — Wed, 27 Feb 2019 22:51:33 GMT

date_wanted = datepart(Occ_time);

ChrisNZ — Wed, 27 Feb 2019 22:56:39 GMT

Like this?

data tr;
  XLS_DATE=42635.375; 
  SAS_DATE=intnx('year',int(XLS_DATE),-60,'s')-1;
  putlog SAS_DATE= date9.;
run;

SAS_DATE=22SEP2016

SAS dates start on 01JAN1960, Excel dates start on 01JAN1900.

CHL0320 — Thu, 28 Feb 2019 14:39:51 GMT

Thank you all for your help. I have found an article and be also to convert Excel date to SAS date and then present it in a format of datetime16.

In brief,

SAS_date_time=(Excel_date_time-21916)*86400.

Here is that article.