topic Re: Convert a weird string to a date-time format in SAS Programming

Convert a weird string to a date-time format

DocMartin — Wed, 19 Aug 2020 16:27:42 GMT

I'm getting raw data files from a source that provides weird (albeit consistent) date-time values as such:

admit

2020-08-06T02:04:00-04:00

2020-09-03T21:29:00-04:00

It's a YYYY-MM-DD date, followed by "T", then a HH:MM:DD time, followed by "-04:00". What I want to have is a variable called new_admit to appear as DDMMMYYYY:HH:MM:SS with the HH being in military time. I'd also like this to be a numeric variable, such that I could subtract or add it to another variable of the same type.

For example, the two values for variable "admit" above would become "new_admit" 06AUG2020:02:04:00 and 03SEP2020:21:29:00.

Thanks!

Andrew

Re: Convert a weird string to a date-time format

novinosrin — Wed, 19 Aug 2020 16:41:12 GMT

Guru @data_null__ wrote this solution in another thread-


data have;
input datetm $30.;
cards;
2020-08-06T02:04:00-04:00
2020-09-03T21:29:00-04:00
;

data want;
 set have;
 new_date=input(datetm,e8601dt.);
 format new_date datetime20.;
run;

Re: Convert a weird string to a date-time format

ballardw — Wed, 19 Aug 2020 16:44:01 GMT

That looks like the E8601DT19. informat should work to convert a character value to a datetime

newvar = input(admit,e8601dt19.);

format newvar datetime18.;

The above ignores the timezone as that appears to be your request.

You really wouldn't "add" likely variables of a similar type (datetime) but use the INTNX function to advance/decrease the values.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.

Re: Convert a weird string to a date-time format

PaigeMiller — Wed, 19 Aug 2020 16:51:23 GMT

The problem with the informat e8601dt. is that it ignores the time zone adjustment which appears in the text string as -04:00

I would think the e8601dz. informat is more appropriate, it adjusts the datetime value by -4 hours and zero minutes, and is probably more correct in this situation.

Re: Convert a weird string to a date-time format

ballardw — Wed, 19 Aug 2020 17:05:16 GMT

@PaigeMiller wrote:

The problem with the informat e8601dt. is that it ignores the time zone adjustment which appears in the text string as -04:00

I would think the e8601dz. informat is more appropriate, it adjusts the datetime value by -4 hours and zero minutes, and is probably more correct in this situation.

Agree in general but the specific request appears to want to discard the timezone component. At least using the E8601DZ informat I couldn't get the desired results because my timezone is quite a bit off. (actually getting different day than requested for the second value)

Re: Convert a weird string to a date-time format

PaigeMiller — Wed, 19 Aug 2020 17:11:44 GMT

@ballardw wrote:

@PaigeMiller wrote:

The problem with the informat e8601dt. is that it ignores the time zone adjustment which appears in the text string as -04:00

I would think the e8601dz. informat is more appropriate, it adjusts the datetime value by -4 hours and zero minutes, and is probably more correct in this situation.

Agree in general but the specific request appears to want to discard the timezone component. At least using the E8601DZ informat I couldn't get the desired results because my timezone is quite a bit off. (actually getting different day than requested for the second value)

My concern is that the OP is unaware of the meaning of -04:00. Anyway, he or she can choose whether to use E8601DT. or E8601DZ.