topic Re: Splitting a date/time string with no delimiter in SAS Programming

Splitting a date/time string with no delimiter

fordcr2 — Thu, 21 Feb 2019 15:23:16 GMT

Hello,

I have seen some posts about splitting text strings into multiple variables, including some date/time strings, but I have not found a solution that would work for my problem. I am importing device data that has multiple variables and one variable for date and time (called DATE_TIME). I need the string to be parsed out into date (formatted like 02/21/2019, so mmddyy10.) and time.

An example of the string I am working with:

20181002105310

This would be 10/02/2018 at 10:53:10.

If it is helpful - there are about 728 observations in this dataset, and I need to do this for two variables (DATE_TIME and TRANSMISSION_DATE_TIME) so it would be nice to be able to do this efficiently if possible 🙂

Re: Splitting a date/time string with no delimiter

PaigeMiller — Thu, 21 Feb 2019 15:31:10 GMT

Input the string as a numeric SAS date/time value using the ANYDTDTM informat.

https://documentation.sas.com/?docsetId=leforinforref&docsetTarget=p1hsn1ji141r4zn0z3xm2dthop6a.htm&docsetVersion=9.4&locale=en

An example is shown at that link.

From there, parsing out the date is done easily with the DATEPART function, parsing out the time is done easily with the TIMEPART function.

Re: Splitting a date/time string with no delimiter

Kurt_Bremser — Thu, 21 Feb 2019 15:30:27 GMT

Try

date = input(substr(string,1,8),yymmdd8.);
time = input(substr(string,9,6),time6.);
format
  date yymmddd10.
  time time8.
;

Re: Splitting a date/time string with no delimiter

Tom — Thu, 21 Feb 2019 15:30:56 GMT

You will probably need to convert the data and time parts separately.

data test;
 str='20181002105310';
 date = input(str,yymmdd8.);
 time = input(substr(str,9),hhmmss6.);
 format date yymmdd10. time time8.;
 put (_all_) (=);
run;

str=20181002105310 date=2018-10-02 time=10:53:10

Re: Splitting a date/time string with no delimiter

fordcr2 — Thu, 21 Feb 2019 15:40:46 GMT

This works great! Do I need to make a macro so that the str variable will update?

Ex) the first DATE_TIME is 20181002105310 which comes out to date: 2018-10-02 time: 10:53:10

the next DATE_TIME is 20170509073215 and I would need the same type of output for date and time, is there a way to make a loop where SAS will just read all of these DATE_TIME variables and convert them accordingly?

Re: Splitting a date/time string with no delimiter

Kurt_Bremser — Thu, 21 Feb 2019 16:54:48 GMT

A data step is a loop fed by input data (either from a dataset or from an external file).

Re: Splitting a date/time string with no delimiter

fordcr2 — Thu, 21 Feb 2019 19:13:23 GMT

You're misunderstanding what I said. I was regarding the "str" variable for the string. Right now what that would do is autofill the same str variable for each DATE_TIME variable and subsequently each new output for date and time. I am asking how to make it so that a new str value will be produced for each DATE_TIME variable input.

Re: Splitting a date/time string with no delimiter

fordcr2 — Thu, 21 Feb 2019 19:16:22 GMT

I figured it out using this code - thank you!

Changed it to:

Data PARI; set xml_files;
date = input(DATE_TIME,yymmdd8.);
time = input(substr(DATE_TIME,9),hhmmss6.);
format date mmddyy10. time time8.;
put (_all_) (=);
run;