Date from Unusual Datetime

[psrajput]

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?

[novinosrin]

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.;

[psrajput]

Invalid argument to function INPUT at line 30 column 8.

[ballardw]

---

@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.

[psrajput]

Thanks novin.

[Tom]

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.);

[psrajput]

Thanks, Tom. It was very close.

[Kurt_Bremser · Accepted solution]

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;

[psrajput]

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?

[Kurt_Bremser]

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.