Parsing out and formatting a time variable from a yyyymmddhhmmss numeric string

[fordcr2]

Hi, 

I have a variable (date_time) that is formatted as yyyymmddhhmmss, but it is read into SAS as numeric type length 8 format BEST. This comes from an excel sheet, and the number is being read into SAS like this: 2.0200507E13, when on the excel sheet it is really 20200507202046 (meaning 05/07/2020 20:20:46). 

I need to parse out and format the time from this variable so that I can make a new variable for time that is formatted like this: 20:20:46.

TIA! 

[s_lassen]

If you are accessing Excel with a libname, you can use the DBSASTYPE data set option to get the value as a character, and then convert using an informat:

data want;
  set <excel library>.have(dbsastype=(date_time='CHAR(14)'));
  /* code to convert to a datetime variable */
run;

It is possible that you can get away with using "DATETIME" instead of "CHAR(14)", so that the conversion is automatic. But I do not have any Excel stuff around to test on. 

[Tom · Accepted solution]

Note that SAS will NOT convert an Excel datetime value into such a number.  But that is something that happens in Excel a lot since it let's you format each cell independently instead of forcing one column to have a single type.  For example when opening a CSV file with such strings in them.

To convert the number into a datetime or time value first convert it into back into a string.  You can then read it using the right informat.   Here is one you using your example value:

data test;
  num = 20200507202046 ;
  str = put(num,z14.);
  dt = input(str, B8601DJ14.);
  time = timepart(dt);
  format num z14. dt datetime19. time tod8.;
  put (_all_) (=/);
run;

We can create these results:

num=20200507202046
str=20200507202046
dt=07MAY2020:20:20:46
time=20:20:46

You could nest the function calls instead of showing each step independently.

data want;
  set have;
  time = timepart(input(put(num,z14.),B8601DJ14.));
  format num z14. time tod8.;
run;

[fordcr2]

This works, but how could I get it to reformat the date as mm/dd/yyyy instead?

[Tom]

---

@fordcr2 wrote:

This works, but how could I get it to reformat the date as mm/dd/yyyy instead?

---

Do you want the DATE part or the TIME part? Or both? To get the date part from the full string just read the first 8 characters.

date = input(str,yymmdd8.);

You can use the MMDDYY format to display date values in MDY order (and confuse half of the people that will read your reports) then just attach that format to your new variable.

format date mmddyy10.;

[ballardw]

I will say that your example of the Excel data is inaccurate. Excel date/time values have the Time portion as a decimal. And 05/07/2020 20:20:46 is actually 43958.8477546296.

So you have an INTEGER masquerading as a date value. So that is how SAS treated it.

You need to create something that can be read properly, i.e. character value:

data example;
  x = 20200507202046 ;
  y = put(x,best14. -L);
  z = dhms(input(y,yymmdd8.),input(substr(y,9,2),f2.),input(substr(y,11,2),f2.),input(substr(y,13,2),f2.));
  format z datetime19.;
   
run;

[fordcr2]

It is not inaccurate. The data comes from a medical device company, and they told us that is how the data is formatted. The original excel file came from a bunch of text files converted to xlsx using R. I converted the excel file back to a csv so now it comes into SAS as:
date_time | Type: Num | Len: 8 | Format: BEST12. | Informat: BEST32.

I need date and time to be separate variables, and I need the time to be formatted like hh:mm:ss.

[Tom]

If you are reading a text (CSV files are text files) from SAS then just write your own data step to read it.  Then you can read the column as text or use the informat you want. Or include steps to convert from string to date.

[ballardw]

---

@fordcr2 wrote:
It is not inaccurate. The data comes from a medical device company, and they told us that is how the data is formatted. The original excel file came from a bunch of text files converted to xlsx using R. I converted the excel file back to a csv so now it comes into SAS as:
date_time | Type: Num | Len: 8 | Format: BEST12. | Informat: BEST32.

I need date and time to be separate variables, and I need the time to be formatted like hh:mm:ss.

---

So, what was the rationale in converting a likely perfectly good file to Excel?

If you are going to be reading multiple files like this you will in the long run be way better off writing a data step to read the text file.

Every single time you "import" an Excel file SAS makes decisions at to variable type and length based on the contents of the first 20 rows of the file. Which means that variables can change types, lengths and in the case of actual valid formatted dates, the value type and format.

Since the medical device company apparently did not apply any actual date or time delimiters such as : or / then you have to read the data correctly.

[fordcr2]

There were hundreds of xml files that are pushed to a server, so the files weren't in a usable format when they were pushed to us. They needed to be put into one file - so we put them into an excel file. Every time I import it into SAS the date_time variable comes in as a numeric variable, but this is good to know thanks.

[ballardw]

---

@fordcr2 wrote:
There were hundreds of xml files that are pushed to a server, so the files weren't in a usable format when they were pushed to us. They needed to be put into one file - so we put them into an excel file. Every time I import it into SAS the date_time variable comes in as a numeric variable, but this is good to know thanks.

---

You might want to see if the SAS XML libname can access those and avoid a lot of work.

Try something like

Libname somelib xml "your file path\your file.xml";

Then run

Proc Datasets libname=somelib;

run;

quit;

If the libname works for your data then the proc datasets output will have a list of datasets, may only be one, of the data.

At which point you can process that data. But it is likely that date time is still just an integer that requires some processing. But at least you have cut out a few steps.