topic Re: How to convert 15 Sep 2018 12:00 character to Datetime? in SAS Programming

How to convert 15 Sep 2018 12:00 character to Datetime?

WorkingMan — Tue, 22 Jan 2019 04:25:17 GMT

Hi. I am currently trying to convert data that is in:
15 Sep 2018 12:00:00 Character to Datetime.

I have managed to successfully convert Character 15/08/2018 12:00:00 to Datetime but not 15 Sep 2018 12:00.

The data was originally in Excel. When i load 15/08/2018 12:00 in, i will get numbers such as (Numbers May differ)

42541.843148148

Here is my code:

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
;

However, I am reading another excel now but my character date is now:

15 Sep 2018 12:00

The same code above will not be able to convert the value to datetime and only produce blank value.

data have ;
  datestring = "15 Sep 2018 12:00" ;
run ;

How should i handle this?

Re: How to convert 15 Sep 2018 12:00 character to Datetime?

WorkingMan — Tue, 22 Jan 2019 03:38:43 GMT

I have posted it in the wrong section. Can any moderator move my thread to "Programming" section?

Re: How to convert 15 Sep 2018 12:00 character to Datetime?

Kurt_Bremser — Tue, 22 Jan 2019 06:52:30 GMT

data have;
  datestring = "15 Sep 2018 12:00";
run;

data want;
set have;
datenum = input(compress(substr(datestring,1,11),' '),date9.);
format datenum yymmddd10.;
run;

The substr extracts the datepart, compress removes the blanks, input with date9. converts to a SAS date.

Re: How to convert 15 Sep 2018 12:00 character to Datetime?

hashman — Tue, 22 Jan 2019 07:26:31 GMT

@WorkingMan:

Use the ANYDTDTMw. informat:

data _null_ ;                               
  datestring = "15 Sep 2018 12:00" ;        
  datetime = input (datestring, anydtdtm.) ;
  /* test */
  put datetime= / datetime=datetime. ;                  
run ;

You'll see that the step prints:

datetime=1852632000

datetime=15SEP18:12:00:00

The first is the actual SAS system datetime stored value (the number of seconds since the beginning of 1960) to which the ANYDTDTMw. informat converted datestring; the second is this value formatted by the DATETIMEw. format.

Best

Paul D.

Re: How to convert 15 Sep 2018 12:00 character to Datetime?

ballardw — Tue, 22 Jan 2019 18:54:10 GMT

@WorkingMan wrote:

Hi. I am currently trying to convert data that is in:
15 Sep 2018 12:00:00 Character to Datetime.

I have managed to successfully convert Character 15/08/2018 12:00:00 to Datetime but not 15 Sep 2018 12:00.

The data was originally in Excel. When i load 15/08/2018 12:00 in, i will get numbers such as (Numbers May differ)
42541.843148148
Here is my code:
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
;
However, I am reading another excel now but my character date is now:

15 Sep 2018 12:00

The same code above will not be able to convert the value to datetime and only produce blank value.
data have ;
  datestring = "15 Sep 2018 12:00" ;
run ;
How should i handle this?

If you are actually seeing in your SAS data set then likely the approach you used for reading the data from Excel to SAS is the main culprit. That numeric value of 42541.843148148 likely indicates a number of days since 1/1/1900 and then a fraction of a day as the way Excel stores datetime values. I might suggest going back to the step that read the data from Excel, save the data as CSV after making sure that all of the columns with date or times have the same Excel display set for the column. Then read/import the CSV. If using Proc import set a large value for GUESSINGROWS to have a better chance of proper values. If you will be reading multiple files of the same structure I recommend taking the time to write a proper data step to read a CSV file. Import direct from Excel can only read a few rows of data to set types and user behaviors can complicate your life tremendously if the Excel has manually entered or edited data cells.