Fast/Unload DATESTAT analysis: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (more conversion cleanup)
m (minor formatting)
 
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<!-- Page name: Fast/Unload DATESTAT analysis-->
==DATESTAT sampling==
If the <var>[[Fast/Unload Extraction_Language (FUEL)#DATESTAT [SUMMARY | DETAIL]|DATESTAT]]</var> statement is present in the <code>FUNIN</code> data set,
If the <var>[[Fast/Unload Extraction Language (FUEL)#datestat|DATESTAT]]</var> statement is present in the <code>FUNIN</code> data set,
<var class="product">Fast/Unload</var> will determine which fields in the file contain date
<var class="product">Fast/Unload</var> will determine which fields in the file contain date
values.
values.
Line 12: Line 12:
At the end of each pass, certain fields are retained for date
At the end of each pass, certain fields are retained for date
analysis and reporting. A field is retained if:
analysis and reporting. A field is retained if:
<ol>
<ul>
<li>For pass one, the field is not found in the 1000 records. </li>
<li>For pass one, the field is not found in the 1000 records. </li>


<li>At least one field value that conforms to a date format is found in the pass, except:
<li>At least one field value that conforms to a date format is found in the pass being performed, except:
<p class="note">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.</p></li>
<p class="note">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 of the field 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.</p></li>
</ol>
 
<p>
<li>The field is not a <var>Blob</var> field. Such fields are not candidates for <var>DATESTAT</var> analysis.</li>
Note that Blob fields are not candidates for <var>DATESTAT</var> analysis.</p>
</ul>
<p>
<p>
The analysis keeps tracks of various totals that are used in
The analysis keeps tracks of various totals that are used in
Line 27: Line 27:
If these overflow, the least-recently-found year and non-date
If these overflow, the least-recently-found year and non-date
samples are discarded, and the most-recently-found date format
samples are discarded, and the most-recently-found date format
sample is discarded.
sample is discarded. </p>
<p class="note"><b>Note:</b> Since only as many as 22 characters of non-date values are kept,  
<p class="note"><b>Note:</b> 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
two different values that are the same in the first 22 characters
are considered to be the same value.</p>
are considered to be the same value.</p>
<p>
<p>
The date formats that are searched for consist of the following list,
The date formats that are searched are listed below, in somewhat arbitrary groupings for visual distinction. Each item in the list represents two formats: </p>
plus each one of the formats in this list, followed by a blank and
<ul>
time in the form HH:MI:SS, if the date format contains a
<li>Format one is the terms that precede the square brackets.</li>
character other than M, D, or Y, or followed immediately by
<li>Format two is format one plus the immediately following square-bracketed contents.</li>
time in the form HHMMSS otherwise.</p>
</ul>
<p class="code">MON DAY YY
<p class="code">MON DAY YY[ HH:MI:SS]
MON DAY YYYY
MON DAY YYYY[ HH:MI:SS]
DAY MON YY
DAY MON YY[ HH:MI:SS]
DAY MON YYYY
DAY MON YYYY[ HH:MI:SS]
DAY MONTH YY
DAY MONTH YY[ HH:MI:SS]
DAY MONTH YYYY
DAY MONTH YYYY[ HH:MI:SS]
DAY MON, YY
DAY MON, YY[ HH:MI:SS]
DAY MON, YYYY
DAY MON, YYYY[ HH:MI:SS]
DAY MONTH, YY
DAY MONTH, YY[ HH:MI:SS]
DAY MONTH, YYYY
DAY MONTH, YYYY[ HH:MI:SS]
DDMMYY
 
DDIMMIYY
DDMMYY[HHMISS]
DDMMYYYY
DDIMMIYY[HHMISS]
DDIMMIYYYY
DDMMYYYY[HHMISS]
MONTH DAY YY
DDIMMIYYYY[HHMISS]
MONTH DAY YYYY
 
MMDDYY
MONTH DAY YY[ HH:MI:SS]
MMIDDIYY
MONTH DAY YYYY[ HH:MI:SS]
MMDDYYYY
MMDDYY[HHMISS]
MMIDDIYYYY
MMIDDIYY[HHMISS]
YYDDD
MMDDYYYY[HHMISS]
YY MON DAY
MMIDDIYYYY[HHMISS]
YY MONTH DAY
YYDDD[HHMISS]
YYMMDD
 
YYIMMIDD
YY MON DAY[ HH:MI:SS]
YYYYDDD
YY MONTH DAY[ HH:MI:SS]
YYYY MON DAY
 
YYYY MONTH DAY
YYMMDD[HHMISS]
YYYYMMDD
YYIMMIDD[HHMISS]
YYYYIMMIDD
YYYYDDD[HHMISS]
 
YYYY MON DAY[ HH:MI:SS]
YYYY MONTH DAY[ HH:MI:SS]
 
YYYYMMDD[HHMISS]
YYYYIMMIDD[HHMISS]
</p>
</p>
For the meaning of the components of these formats, see
For the meaning of the components of these formats, see
[[Fast/Unload datetime processing considerations#dtfmt|Datetime formats]].
[[Fast/Unload datetime processing considerations#dtfmt|Datetime formats]].
 
<div id="dtrep"></div>
==<b id="dtrep"></b>DATESTAT reporting==
==DATESTAT reporting==
<!--Caution: <div> above-->
This section describes the reports created by the <var>DATESTAT SUMMARY</var>
This section describes the reports created by the <var>DATESTAT SUMMARY</var>
and <var>DATESTAT DETAIL</var> statements.
and <var>DATESTAT DETAIL</var> statements.
Both of these statements indicate something about the "quality" of the
Both of these statements indicate something about the "quality" of the
date data in the field.
date data in the field.  
The purposes of "date field quality" are:
<p>
The purposes of "date field quality" are:</p>
<ul>
<ul>
<li>If you are running <var>DATESTAT SUMMARY</var>, and you have some values other
<li>If you are running <var>DATESTAT SUMMARY</var>, and you have some values other
Line 93: Line 97:
</ul>
</ul>


The quality is expressed as <b>pure</b>,
The quality is expressed as <b>pure</b>, <b>good</b>, <b>fair</b>, or <b>poor</b>. It is
<b>good</b>, <b>fair</b>, or <b>poor</b>. It is
an attempt to measure the possible level of effort required to correct
an attempt to measure the possible level of effort required to correct
data on the file.
data on the file.
Line 110: Line 113:
</ul>
</ul>
<p>
<p>
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 <var class="product">Fast/Unload</var> prints the string "Common date format not found".</p></td></tr>
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 <var class="product">Fast/Unload</var> prints the string <code>Common date format not found.</code></p></td></tr>


<tr><th>Fair</th>
<tr><th>Fair</th>
<td>Either of these:  
<td>Either of these:  
<ul>
<ul>
<li>There are more than 5 distinct non-date values and more than .001% of the field occurrences are non-date values.</li>
<li>There are more than 5 distinct non-date values, and more than .001% of the field occurrences are non-date values.</li>


<li>The percentage of date values that have uncommon formats, times the number of uncommon formats, is greater than .001%.</li>
<li>The percentage of date values that have uncommon formats, times the number of uncommon formats, is greater than .001%.</li>
Line 124: Line 127:
</table>
</table>


==DATESTAT SUMMARY==
===DATESTAT SUMMARY===
<var>DATESTAT SUMMARY</var> creates a report with 1-3 lines for each date field, in
<var>DATESTAT SUMMARY</var> creates a report with one to three lines for each date field, in
the following form:
the following form:
<ol>
<ol>
Line 133: Line 136:
<li><i>tot</i> occurrences of field  <i>format count</i> </li>
<li><i>tot</i> occurrences of field  <i>format count</i> </li>


<li><i>nsmp</i> occurrences of nondate value (len <i>len</i>):
<li><i>nsmp</i> occurrences of non-date value (len <i>len</i>):
<i>sample</i> </li>
<i>sample</i> </li>
</ol>
</ol>
<p>
<p>
Where:
Where: </p>
<ol>
<ol>
<li>This line is always present; the components are:
<li>This line is always present; the components are:
<ul>
<ul>
<li><i>ftag</i>
<li><i>ftag</i> is: "Common date format not found," if there are more than
is either "Common date format not found", if there are more than
20 date formats, or it is "Common format:" otherwise. </li>
20 date formats, or "Common format:" otherwise. </li>


<li><i>format</i> is the most commonly occurring date format,
<li><i>format</i> is the most commonly occurring date format,
if <i>ftag</i> is "Common format:", or blank otherwise. </li>
if <i>ftag</i> is "Common format:", or it is blank otherwise. </li>


<li><i>qual</i> is either "pure", "good", "fair", or "poor". </li>
<li><i>qual</i> is "pure," "good," "fair," or "poor." </li>


<li><i>span</i> is either:
<li><i>span</i> is either of these:
<b>CENTSPAN:</b> <i>YYYY</i>
<ul>
<br/>or <b>No YY occurrences</b>
<li><code>CENTSPAN: <i>YYYY</i></code></li>
<br/>The former gives a recommended CENTSPAN,
if the field has any 2-digit years, where <i>YYYY</i>
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.) </li>


<li><i>n</i>
<li>No <i>YY</i> occurrences</li>
is a sequential numbering of the fields; this can be correlated to
</ul>
an FSTATS report. </li>
<p>
The former gives a recommended <var>CENTSPAN</var>,
if the field has any two-digit years, where <i>YYYY</i>
is the oldest two-digit year found.
The latter occurs if the field does not have any two-digit years.
Two-digit years are interpreted using a <var>CENTSPAN</var> of <code>1900</code>, or, if running <var>DATESTAT</var> after 1999, a <var>CENTSPAN</var> of <code>-99</code>. </p></li>
 
<li><i>n</i> is a sequential numbering of the fields; this can be correlated to
an <var>FSTATS</var> report. </li>


<li><i>name</i> is the name of the field. </li>
<li><i>name</i> is the name of the field. </li>
Line 171: Line 174:
The components are:
The components are:
<ul>
<ul>
<li><i>tot</i> is the total number of occurrences of the field in the file </li>
<li><i>tot</i> is the total number of occurrences of the field in the file. </li>


<li><i>format count</i> is either <i>n</i> <b>different formats found</b>, if
<li><i>format count</i> is: <i>n</i> <b>different formats found</b> (if
<i>n</i> is more than 1, or blank otherwise. </li>
<i>n</i> is more than 1), or it is blank otherwise. </li>
</ul> </li>
</ul> </li>


Line 180: Line 183:
The line has one of the two following forms:
The line has one of the two following forms:
<ul>
<ul>
<li><i>nsmp</i> occurrences of nondate value (<i>len</i>):
<li><i>nsmp</i> occurrences of non-date value (<i>len</i>):
<i>truncated_value</i> <i>trunc_flag</i> </li>
<i>truncated_value</i> <i>trunc_flag</i> </li>


Line 187: Line 190:
<p>
<p>
Where <i>nsmp</i> is the number of occurrences of a non-date value, if it appears
Where <i>nsmp</i> is the number of occurrences of a non-date value, if it appears
to be exactly one non-date value in the field, and <i>len</i> is its length, and
to be exactly one non-date value in the field; <i>len</i> is its length; and
<i>truncated_value</i> is the first 22 characters of its value.
<i>truncated_value</i> is the first 22 characters of its value.
<i>trunc_flag</i> is the first 22 bytes of its value, if the length is greater than 22.
<i>trunc_flag</i> is the first 22 bytes of its value, if the length is greater than 22.
Line 193: Line 196:
the same.</p>
the same.</p>
<p>
<p>
If there are two or more non-date values in the field, then the
If there are two or more non-date values in the field, the second form of this line is presented, where <i>dist</i> is the number of different non-date values that Fast Unload has kept as samples (up to the maximum of 20).</p> </li>
second form of this line is presented, where <i>dist</i>
is the number of different non-date values that Fast Unload has kept as samples (up to the maximum of 20).</p> </li>
</ol>
</ol>
<p class="note"><b>Note:</b> For the recommended <var>CENTSPAN</var>,
<p class="note"><b>Note:</b> For the recommended <var>CENTSPAN</var>,
Line 201: Line 202:
the date of the run up to and including the date of the run. </p>
the date of the run up to and including the date of the run. </p>


==DATESTAT DETAIL==
===DATESTAT DETAIL===
DATEST DETAIL creates a report with 1 page for each date field, which
<var>DATESTAT DETAIL</var> creates a report with one page for each date field, which
includes:
includes:
<ol>
<ol>
<li>The number of field occurrences.
<li>The number of field occurrences. </li>
 
<li>The number of date occurrences, with the minimum and maximum date
<li>The number of date occurrences, with the minimum and maximum date
value, for both 4 digit ("YYYY") and 2 digit ("YY") years.
value, for both four-digit ("YYYY") and two-digit ("YY") years. </li>
<li>A sample of the discovered date formats.
 
<li>A sample of the year values occurring in the field.
<li>A sample of the discovered date formats. </li>
<li>A sample of the non-date values occurring in the field.
 
<li>A sample of the year values occurring in the field. </li>
 
<li>A sample of the non-date values occurring in the field. </li>
</ol>
</ol>
<p></p>
<p>
The sampling rules are described in [[Fast/Unload DATESTAT analysis]]; some values
The sampling rules are described in [[#DATESTAT sampling|DATESTAT sampling]]; some values
may be discarded.
may be discarded. An asterisk (<tt>*</tt>) is printed after a sample if
An asterisk (<code>*</code>) is printed after a sample if
the sample occurrence count is incomplete.</p>
the sample occurrence count is incomplete.
<blockquote  class="note">
<p></p>
<b>Note:</b> For the recommended <var>CENTSPAN</var> and the minimum/maximum values of the <var>DETAIL</var> report, <var class="product">Fast/Unload</var> assumes that all two-digit years occur either:
Note that for the recommended
<ul>
CENTSPAN and the min/max values of the DETAIL report,
<li>In the 1900s</li>
<var class="product">Fast/Unload</var> assumes that all 2-digit years occur in the 1900s, or, if running
<li>99 years before the date of the run (up to and including the date of the run), <b><i>if</i></b> running <var>DATESTAT</var> after 1999 </li>
DATESTAT after 1999, 99 years before
</ul>
the date of the run up to and including the date of the run.
</blockquote>
 
==See also==
==See also==
[[Fast/Unload overview#WIKFUN$$topics|Fast/Unload topics]]
{{Template:Fast/Unload topic list}}

Latest revision as of 22:45, 4 August 2016

DATESTAT sampling

If the 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:

  • For pass one, the field is not found in the 1000 records.
  • At least one field value that conforms to a date format is found in the pass being performed, 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 of the field 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.

  • The field is not a Blob field. Such 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 are listed below, in somewhat arbitrary groupings for visual distinction. Each item in the list represents two formats:

  • Format one is the terms that precede the square brackets.
  • Format two is format one plus the immediately following square-bracketed contents.

MON DAY YY[ HH:MI:SS] MON DAY YYYY[ HH:MI:SS] DAY MON YY[ HH:MI:SS] DAY MON YYYY[ HH:MI:SS] DAY MONTH YY[ HH:MI:SS] DAY MONTH YYYY[ HH:MI:SS] DAY MON, YY[ HH:MI:SS] DAY MON, YYYY[ HH:MI:SS] DAY MONTH, YY[ HH:MI:SS] DAY MONTH, YYYY[ HH:MI:SS] DDMMYY[HHMISS] DDIMMIYY[HHMISS] DDMMYYYY[HHMISS] DDIMMIYYYY[HHMISS] MONTH DAY YY[ HH:MI:SS] MONTH DAY YYYY[ HH:MI:SS] MMDDYY[HHMISS] MMIDDIYY[HHMISS] MMDDYYYY[HHMISS] MMIDDIYYYY[HHMISS] YYDDD[HHMISS] YY MON DAY[ HH:MI:SS] YY MONTH DAY[ HH:MI:SS] YYMMDD[HHMISS] YYIMMIDD[HHMISS] YYYYDDD[HHMISS] YYYY MON DAY[ HH:MI:SS] YYYY MONTH DAY[ HH:MI:SS] YYYYMMDD[HHMISS] YYYYIMMIDD[HHMISS]

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 one to three 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 non-date value (len len): sample

Where:

  1. This line is always present; the components are:
    • ftag is: "Common date format not found," if there are more than 20 date formats, or it is "Common format:" otherwise.
    • format is the most commonly occurring date format, if ftag is "Common format:", or it is blank otherwise.
    • qual is "pure," "good," "fair," or "poor."
    • span is either of these:
      • CENTSPAN: YYYY
      • No YY occurrences

      The former gives a recommended CENTSPAN, if the field has any two-digit years, where YYYY is the oldest two-digit year found. The latter occurs if the field does not have any two-digit years. Two-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: n different formats found (if n is more than 1), or it is 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 non-date 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; 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, 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

DATESTAT DETAIL creates a report with one 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 four-digit ("YYYY") and two-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 DATESTAT sampling; some values may be discarded. An asterisk (*) is printed after a sample if the sample occurrence count is incomplete.

Note: For the recommended CENTSPAN and the minimum/maximum values of the DETAIL report, Fast/Unload assumes that all two-digit years occur either:

  • In the 1900s
  • 99 years before the date of the run (up to and including the date of the run), if running DATESTAT after 1999

See also