topic Re: Date from Unusual Datetime in SAS Programming

Date from Unusual Datetime

psrajput — Wed, 09 Jun 2021 14:51:24 GMT

Hi,

I have a variable with the unconventional datetime values such as 19MAR2017:03:56:59.210011

I know datepart won't work as it is not a datetime rather it is in char format, is there any other way to get the date (19MAR2017) from it?

Re: Date from Unusual Datetime

novinosrin — Wed, 09 Jun 2021 14:54:46 GMT

The date part i.e. 19MAR2017 is pretty conventional in the presumed unconventional data- 19MAR2017:03:56:59.210011

Therefore, you could just read 9 bytes with a date9. informat like-

need_date=input(your_date_var, date9.);

format need_date date9.;

Re: Date from Unusual Datetime

Tom — Wed, 09 Jun 2021 15:19:53 GMT

If the values always have 2 digits for the day of the month just use INPUT() on the original string.

date = input(datetime_string,date9.);
format date date9.;

If you have values like

1MAR2017:03:56:59.210011

then use SCAN() to get the part up to the first colon.

date = input(scan(datetime_string,1,':'),date9.);

Re: Date from Unusual Datetime

psrajput — Wed, 09 Jun 2021 15:20:12 GMT

Invalid argument to function INPUT at line 30 column 8.

Re: Date from Unusual Datetime

Kurt_Bremser — Wed, 09 Jun 2021 15:20:29 GMT

Not unconventional, this is the default SAS datetime format.

See this:

data test;
dt_char = "19MAR2017:03:56:59.210011";
dt_num = input(dt_char,datetime26.);
date_num = datepart(dt_num);
format dt_num e8601dt26.6 date_num yymmdd10.;
run;

Re: Date from Unusual Datetime

psrajput — Wed, 09 Jun 2021 15:30:05 GMT

Thanks, Tom. It was very close.

Re: Date from Unusual Datetime

psrajput — Wed, 09 Jun 2021 15:32:04 GMT

Thanks @Kurt, What is the last part .59.210011. Is it ms? Also, if it is the default format, why doesn't sas have a function like datepart for it?

Re: Date from Unusual Datetime

psrajput — Wed, 09 Jun 2021 15:32:40 GMT

Thanks novin.

Re: Date from Unusual Datetime

ballardw — Wed, 09 Jun 2021 15:50:35 GMT

@psrajput wrote:

Invalid argument to function INPUT at line 30 column 8.

You really should show the value of the variable that created that message.

I'm going out on a limb and guessing it is something like 29Feb in a non-leap year or 31Nov, invalid day for the month.

You should post the diagnostic message as well, not just the error. Consider this small example that generates a similar error:

52   data example;
53      x='19MAR2017:03:56:59.210011';
54      y='29Feb2017:03:56:59.210011';
55      datex = input(x,date9.);
56      datey = input(y,date9.);
57      format datex datey date9.;
58   run;

NOTE: Invalid argument to function INPUT at line 56 column 12.
x=19MAR2017:03:56:59.210011 y=29Feb2017:03:56:59.210011 datex=19MAR2017 datey=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 56:12
NOTE: The data set WORK.EXAMPLE has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

The highlighted line above shows diagnostic values of the variable. In this case it is moderately easy to tell that the invalid value for Line 56, the Datey is an invalid date value. With that value you will NEVER get a valid SAS date variable because the date doesn't exist.

I learned some of this nearly 30 years ago reading a data set that had such wonderful dates as 67NOV1978.

Re: Date from Unusual Datetime

Kurt_Bremser — Wed, 09 Jun 2021 16:52:08 GMT

19MAR2017

is the date in DATE9. format, which is also the format you use in SAS to write date literals (constants):

"19MAR2017"d

Next, a colon separates date from time, which you get as hours, minutes and seconds separated by colons, and the fractional part of a second after a dot.

So the

.210011

are in fact microseconds.

The character string you have is written in the "default" DATETIME format that you would also use for a datetime constant in code:

"19MAR2017:03:56:59.210011"dt

But this string is just a string, not a datetime value yet. Datetime values in SAS are numbers, counting the seconds from 1960-01-01:00:00:00. The DATEPART function needs such a value as argument, it does not work on character strings.