Trailing numerals in SAS Date/Time/DateTime formats are lost upon read

[dgkf]

Note: I'm using the haven package in R to read in the data. I tried to trace the formatting as far down into C as my limited C knowledge allows, but it's very possible the information loss is happening on the R side.

SAS datetimes with a numeric qualifier (eg TIME5.) are read in without the trailing numeral (TIME). Would it be possible to include the numeric part of the format string?

Steps to Reproduce

1. Create a SAS dataset with date, time or datetime variables with a specified format length. (SAS script and resulting dataset attached (sas_datetime_example_and_data.zip) with columns for each of SAS's datetime formats using both character and numeric internal representations.)
2. Read in data in R

   df <- haven::read_sas("datetimes.sas")
   
   # extract readstat format from SAS column created using
   # FMT_TIME5=input(20999, TIME5.); format FMT_TIME5: TIME5.;
   attr(df[["FMT_TIME5"]], "format")  
   # TIME   # expected "TIME5"

Full set of formats which omit the numeric component

SAS Format (9.04)	R haven Format
DATE9	DATE
DDMMYY10	DDMMYY
DDMMYYB10	DDMMYYB
DDMMYYC10	DDMMYYC
DDMMYYD10	DDMMYYD
DDMMYYN6	DDMMYYN
DDMMYYN8	DDMMYYN
DDMMYYP10	DDMMYYP
DDMMYYS10	DDMMYYS
MMDDYY10	MMDDYY
MMDDYYB10	MMDDYYB
MMDDYYC10	MMDDYYC
MMDDYYD10	MMDDYYD
MMDDYYN6	MMDDYYN
MMDDYYN8	MMDDYYN
MMDDYYP10	MMDDYYP
MMDDYYS10	MMDDYYS
TIME5	TIME
YYQRN7	YYQRN

[dgkf]

A bit of further exploration (thanks to @ofajardo at pyreadstat).

>>> import pyreadstat
>>> df, meta = pyreadstat.read_sas7bdat("datetimes.sas7bdat") 
>>> meta.variable_display_width["FMT_TIME5"]
    0  # I think we expect to see 5

In this case, all variables have a display width of 0, including all the formats in the table above. I tried a few other fields in the pyreadstat metadata, but nothing seemed to capture these trailing numerals.

[evanmiller]

Hi, thank you for the extensive report. It appears that internally, these format strings are stored without the length information. Two possibilities are that this length is stored in a separate record, or that SAS softwware knows what the lengths should be (e.g. if TIME is always TIME5). The latter possibility seems to be contradicted by the existence of DDMMYYN6 and DDMMYYN8, which are both stored as DDMMYY. I'll need to investigate this further.

[evanmiller]

Hi, please try the commit here:

7f63d1f

It would help to have the same script save a 32-bit file as well as a big-endian file, but I realize that may be difficult to achieve.

[dgkf]

This looks great. I was able to replace the ReadStat source that is bundled with haven and read in the format lengths.

It would help to have the same script save a 32-bit file as well as a big-endian file, but I realize that may be difficult to achieve.

I can try to help here, but I'd need a bit of guidance on how to test it. What considerations would I need to account for to produce 32-bit and big-endian files? Is this a result of the SAS host's architecture when the sas7bdat file is saved? If so, I'm not sure I have access to alternative deployments, but I can ask around.

[evanmiller]

@dgkf I think that you'd need SAS running on a 32-bit architecture and a big-endian architecture to produce those files. So I'm willing to leave it alone for now, and simply address 32-bit or big-endian bugs as they are reported.

I'll try to comb through the project's collection of SAS7BDAT files to see if I can find such a file containing one of the date/time fields that you identified.