1.  **Construction of the Annual (Federal Fiscal Year) AFCARS Database**

    This paper describes the process used by the Children's Bureau to
    construct an annual database from the Adoption and Foster Care
    Analysis and Reporting System (AFCARS) data reported by the State
    title IV-B/IV-E agencies. Each annual database will contain
    case-specific information for the unduplicated set of children
    served by the States' foster care systems during the Federal Fiscal
    Year (FFY), beginning October 1 and ending September 30.

    To produce an annual database of foster care children, the
    Children's Bureau must first compare the information sent by States
    for each of the two AFCARS report periods (October-March and
    April-September) in that FFY. On a state-by-state basis, the
    Children's Bureau creates interim extract files, matching case
    records across the two reporting periods. Through matching and
    extraction, an unduplicated annual file is created for each State.
    Finally, the State files are combined to create the annual database.

    Through this process, the annual database counts will include:
    foster care children already in care who stay in care through the
    end of the year (during both report periods); children who enter
    foster care during the year, but are discharged before the end of
    year (in either report period); and children who come into foster
    care, are discharged, and then re-enter care to either remain in
    care or exit care again before the end of the year. Simply put, the
    database contains an unduplicated count of children served by the
    foster care system during the year.

    Before describing this process in more detail, it is important to
    note that the first and second report periods of any FFY are
    identified by the two-digit FFY, followed by the letters A or B,
    respectively. For example, the first FFY 1996 report period (October
    1, 1995-March 31, 1996) is called "96A," and the second report
    period (April 1, 1996-September 30, 1996) is "96B." Also, once all
    the States files have been combined into the annual database, the
    national annual database is identified by the two-digit FFY,
    followed by the letter C. Therefore, in keeping with our example,
    the annual FFY 1996 database is referred to as "96C."

    Each year this same process is used to construct an annual database
    from the semi-annual A and B files. Here are the basic principles of
    AFCARS reporting and analysis, which will help you understand the
    construction of an annual database:

· As long as a child is in foster care, the State must submit an AFCARS
record for every report period during which the child is in care. When
the child is discharged from foster care, the State will submit the
child's record for that report period, along with the discharge
information.

· Each child's AFCARS record has a "Date of Latest Removal." Since this
is the date the child entered foster care for the [current
episode]{.underline}, as long as the child remains in foster care
without being discharged, [this date of latest removal will not
change]{.underline}.

· AFCARS extract files identify, match, and capture unique records
across the two report periods, to avoid having more than one record for
the same foster care child in the total annual database. The extract
file [excludes duplicate records]{.underline} that matched between two
files.

· Focusing on the child as the unit of analysis, the annual database
will [include only the last record received]{.underline} for a child
served. This means that if a child comes into care at the start of the
year, exits care and then later returns to care in the second half of
the year, the record representing this child in the annual database will
be the one submitted in the second report period when the child has
returned to care .

For discussion purposes, we will use the example of the construction of
the FFY 1996 AFCARS annual database. The construction of the FFY 1996
annual database begins with the creation of five separate files using
the 96A and 96B datasets from a particular State: 2 discharge files and
3 entry files.

From the State's 96A dataset, we create only one file, "Dis96A," with
all foster care children discharged during the 96A report period.

From the State's 96B dataset, we create the four other files:

# "E/96Ainc" file of children who entered care before the start of 96A and who are still in care as of the close of 96B;

1.  "E96Ainc" file of children who entered care during 96A and are still
    in care as of the close of 96B;

    "E96Binc" file of children who entered care during 96B and are still
    in care as of the close of 96B; and

> the "Dis96B" file of children who were discharged during 96B.

The naming conventions for these files are as follows:

E/ = Entered care before the start of this report period

E = Entered care during this report period

inc = Still in care at the close of the "B" report period

Dis = Discharged during this report period

Using these five files, a four-step matching process (described below)
begins. The goal of the first three steps is to create a file of
children who were discharged during 96A and did not return to foster
care during the year. The last step checks for 96B duplicates, those
children who were both discharged in 96B and re-entered care in 96B.

[STEP ONE]{.underline}:

Match the Dis96A file against the Dis96B file to remove from the Dis96A
file any children's records who may have returned to foster care and
then been discharged during 96B. The Dis96A records that [do not
match]{.underline} any of the Dis96B records are placed in an extract
file called"Test1." The Test1 file will not contain records for children
who returned to care and then discharged (again) during 96B. Keep in
mind that a child who discharges in 96A will either stay home or return
to foster care, in which case the child may either stay in care or be
discharged again. Although it is possible that this child may return and
be discharged again during the same report period, experience shows that
two discharge records for the same child in a given period is unusual
(generally less than one percent of all cases).

The discharged foster care children in the Dis96A file who did not match
any Dis96B discharges are now in the Test1 extract file. Through
additional matching, this extract file will be further reduced, until we
are sure that only 96A discharged children who did not return to care
during the year are left in the final extract file.

PRINCIPLE: The child's record from the Dis96B file represents the latest
record received for the child and therefore the most appropriate record
for the annual database.

[STEP TWO]{.underline}:

Match the Test1 file against the E96Ainc file. Keep in mind that this
E96Ainc file was created from the State's [96B]{.underline} data. We
remove any matches (children who returned to care in 96A and are still
in care at the end of 96B) from the file to create a new extract file,
"Test2," with the remaining unmatched records. The Test2 extract file
will not contain records of children who were discharged, returned to
care in 96A, and remained in care through the end of 96B.

PRINCIPLE: If a child discharges during 96A, returns to care in 96A, and
is still in care at the close of 96B, then the child's record found in
the 96B data submission represents the latest record received for the
child, which should be included in the annual database.

[STEP THREE]{.underline}:

Match the Test2 file against the E96Binc file. We remove any matches, so
that the remaining unmatched records become a new "Test3" extract file.
The Test3 extract file contains all children who exited care during 96A
and did not return to the foster care system at any time through the end
of 96B.

PRINCIPLE: The record of a child who exited care in 96A, then returns to
care in 96B and remains in care through the end of 96B represents the
latest record received for that child. The records of children who were
discharged from foster care in 96A and did not return to care are now
captured in the extract file, Test3.

[STEP FOUR]{.underline}:

Match the Dis96B against the E96Binc. This matching process will
identify any two records in the same report period on the same child,
one with a discharge date and one without, implying that the child is
still in care. Although unusual, this matching process may find some
matches. Removing any matches, we create a new extract file "Test4" for
the unmatched records of Dis96B. If no matches are found, the Dis96B
file remains unchanged.

PRINCIPLE: The assumption is that the record indicating that the child
is still in care is the latest record received on the child.

When the four steps are completed, the following 5 files are combined to
create the annual file for a particular State:

1\. E/96Ainc

2\. E96Ainc

3\. E96Binc

4\. Test3

5\. Test4 or Dis96B, as appropriate

12. A fifth step may be needed to complete the construction of an annual
    file, in the event that a State submits records in a later
    submission that should have been submitted in an earlier report. For
    instance, using the example of the FFY 1996 foster care database, we
    may find that records submitted in 97A actually contain 96A or 96B
    discharge dates. These records must be matched against the FFY 1996
    annual file that has been constructed. If matches are found, an
    extract file of the annual file is created and merged with the file
    of 97A records containing FFY 1996 discharge dates. Adding these
    late submissions will ensure that the State's annual file is as
    complete as possible.

When this matching process is completed for each State, all of the
annual State files are merged into a single annual file and referred to
as 96C.