Fast/Unload DATESTAT analysis

If the [[Fast/Unload Extraction_Language (FUEL)#DATESTAT [SUMMARY | DETAIL]|DATESTAT]] statement is present in the FUNIN data set, Fast/Unload will determine which fields in the file contain date values. The determination of whether a field contains a date value is done in two passes:

• The first pass examines 1000 evenly-distributed records in the file.
• The second pass analyzes all records.

At the end of each pass, certain fields are retained for date analysis and reporting. A field is retained if:

1. For pass one, the field is not found in the 1000 records.
2. At least one field value that conforms to a date format is found in the pass, except:

   A field is not a date if all dates were all numeric, there were more than 5 distinct non-date values, and fewer than 50% of the instances are dates. This is an attempt to avoid situations in which a field such as zip code could be treated as a 5-digit (YYDDD) date.

Note that Blob fields are not candidates for DATESTAT analysis.

The analysis keeps tracks of various totals that are used in reporting, and also keeps some of the values found in the field. Up to 36 date formats, 20 non-date values, and 120 year-samples will be kept. If these overflow, the least-recently-found year and non-date samples are discarded, and the most-recently-found date format sample is discarded.

Note: Since only as many as 22 characters of non-date values are kept, two different values that are the same in the first 22 characters are considered to be the same value.

The date formats that are searched for consist of the following list, plus each one of the formats in this list, followed by a blank and time in the form HH:MI:SS, if the date format contains a character other than M, D, or Y, or followed immediately by time in the form HHMMSS otherwise.

MON DAY YY MON DAY YYYY DAY MON YY DAY MON YYYY DAY MONTH YY DAY MONTH YYYY DAY MON, YY DAY MON, YYYY DAY MONTH, YY DAY MONTH, YYYY DDMMYY DDIMMIYY DDMMYYYY DDIMMIYYYY MONTH DAY YY MONTH DAY YYYY MMDDYY MMIDDIYY MMDDYYYY MMIDDIYYYY YYDDD YY MON DAY YY MONTH DAY YYMMDD YYIMMIDD YYYYDDD YYYY MON DAY YYYY MONTH DAY YYYYMMDD YYYYIMMIDD

For the meaning of the components of these formats, see Datetime formats.

DATESTAT reporting

This section describes the reports created by the DATESTAT SUMMARY and DATESTAT DETAIL statements. Both of these statements indicate something about the "quality" of the date data in the field. The purposes of "date field quality" are:

• If you are running DATESTAT SUMMARY, and you have some values other than pure, you may want to do further investigation of the date fields.
• To indicate how much work might be involved to resolve the various values stored in the field. One tool to resolve the values is to run DATESTAT DETAIL. Doing some ad-hoc work with SOUL is another approach. The "worse" the quality of a field, the more work is likely to be required to resolve questions about the field values.

The quality is expressed as pure, good, fair, or poor. It is an attempt to measure the possible level of effort required to correct data on the file. The terms have the following meanings:

Pure	There are only date values, and all with a single date format.
Poor	Either of these: There are more than 10 distinct non-date values and more than .01% of the field occurrences are non-date values. The percentage of date values that have uncommon formats, times the number of uncommon formats, is greater than .01%, or there are more than 20 date formats. If there are more than 20 date formats, or if the ratio of occurrences of the most common date format to occurrences of the next most common date format is less than 10 to 1, then Fast/Unload prints the string "Common date format not found".
Fair	Either of these: There are more than 5 distinct non-date values and more than .001% of the field occurrences are non-date values. The percentage of date values that have uncommon formats, times the number of uncommon formats, is greater than .001%.
Good	Anything else.

DATESTAT SUMMARY

DATESTAT SUMMARY creates a report with 1-3 lines for each date field, in the following form:

1. ftag format (qual) span field...n: name
2. tot occurrences of field format count
3. nsmp occurrences of nondate value (len len): sample

Where:

1. This line is always present; the components are:
   • ftag is either "Common date format not found", if there are more than 20 date formats, or "Common format:" otherwise.
   • format is the most commonly occurring date format, if ftag is "Common format:", or blank otherwise.
   • qual is either "pure", "good", "fair", or "poor".
   • span is either: CENTSPAN: YYYY
     or No YY occurrences
     The former gives a recommended CENTSPAN, if the field has any 2-digit years, where YYYY is the oldest 2-digit year found; the latter occurs if the field does not have any 2-digit years. (2-digit years are interpreted using a CENTSPAN of 1900, or, if running DATESTAT after 1999, a CENTSPAN of -99.)
   • n is a sequential numbering of the fields; this can be correlated to an FSTATS report.
   • name is the name of the field.
2. This line is always printed if the qual is not pure. The components are:
   • tot is the total number of occurrences of the field in the file
   • format count is either n different formats found, if n is more than 1, or blank otherwise.
3. This line is always printed if there are any non-date values for the field. The line has one of the two following forms:
   • nsmp occurrences of nondate value (len): truncated_value trunc_flag
   • dist distinct non-date values

   Where nsmp is the number of occurrences of a non-date value, if it appears to be exactly one non-date value in the field, and len is its length, and truncated_value is the first 22 characters of its value. trunc_flag is the first 22 bytes of its value, if the length is greater than 22. In fact, some of the non-date values in the field may differ, if their first 22 characters are the same.

   If there are two or more non-date values in the field, then the second form of this line is presented, where dist is the number of different non-date values that Fast Unload has kept as samples (up to the maximum of 20).

Note: For the recommended CENTSPAN, Fast/Unload assumes that all two-digit years occur in the 1900s, or, if running DATESTAT after 1999, 99 years before the date of the run up to and including the date of the run.

DATESTAT DETAIL

DATEST DETAIL creates a report with 1 page for each date field, which includes:

1. The number of field occurrences.
2. The number of date occurrences, with the minimum and maximum date value, for both 4 digit ("YYYY") and 2 digit ("YY") years.
3. A sample of the discovered date formats.
4. A sample of the year values occurring in the field.
5. A sample of the non-date values occurring in the field.

The sampling rules are described in Fast/Unload DATESTAT analysis; some values may be discarded. An asterisk (*) is printed after a sample if the sample occurrence count is incomplete.

Note that for the recommended CENTSPAN and the min/max values of the DETAIL report, Fast/Unload assumes that all 2-digit years occur in the 1900s, or, if running DATESTAT after 1999, 99 years before the date of the run up to and including the date of the run.

See also

Fast/Unload topics