This technical bulletin supplements information in 45 CFR 1355.40, and the appendices to 45 CFR 1355, on data file format. By following the guidance of this bulletin, States will avoid file format errors that may cause processing failure by the Federal AFCAR System.
Foster Care Summary File: the format for the foster care summary file and how the Federal AFCAR System calculates the summary file that is required to precede the foster care detail file.
Adoption Summary File: the format for the adoption summary file and how the Federal AFCAR System calculates the summary file that is required to precede the adoption detail file.
The data file that is transmitted must be in ASCII. This format is specified in Appendix C of 45 CFR 1355. EBCDIC files will not be processed.
A Summary File must always accompany a Detail File. This applies to both Adoption and Foster Care (Appendix E of 45 CFR 1355).
Appropriate delimiters must be included with each file. The appropriate file delimiters must be included with the corresponding data file. If a particular file is not being submitted, DO NOT INCLUDE THE DELIMITER. For example, if a State is not including Adoption Summary and Detail Files, then the Adoption Delimiter ($) must not be included in the submission.
Case Number field. The case number (Foster Care Element #4 and Adoption Element #3) should be right justified and padded with zeros, if necessary, to fill out the 12 character field.
Counts and dollars. The counts and dollar amounts should be right justified and padded with zeros to fill out the field. A field filled with zeros indicates a count of zero. A field filled with blanks indicates missing data.
EXCEPTION: Adoption Element #36 (Monthly amount of subsidy). If adoption data element #35 is no, then 00000 means no subsidy amount. If adoption data element #35 is yes, then 00000 means the subsidy amount only includes benefits under titles XIX or XX of the Social Security Act.
Data format standards. For detailed information on format standards of individual elements, see Technical Bulletins #6 and #7.
B. File Format Layout
Table 1 includes the files that are to be concatenated, in the proper order, prior to transmission. The shaded row indicates optional information that may be included in the State data file.
AFCARS requires all date formats to accommodate century information, either a CCYYMMDD, CCYYMM or CCYY, where CCYY represents the four digit century year, e.g. 1996. States were required to use the four-digit format, regardless of the reporting period, beginning October 1, 1996 (ACYF-IM-CB-96-08, and 65 FR 4019).
The Adoption Aggregate data file consists of the aggregate numbers of children adopted prior to October 1, 1994, who continued to receive title IV-E subsidies after October 1, 1994. This data is to be reported one time only, and was to be included with the first reporting period (October 1, 1994 - March 31, 1995) data submission. States are required (under 45 CFR 1355.40 (a)(3)) to submit this data. If the State was unable to provide this information with the first data submission (covering the reporting period October 1, 1994 through March 31, 1995) the State should:
Submit an adoption aggregate file with the Adoption Aggregate file delimiter (#) and zeros in all the count fields (adoption data elements #2-#21); and,
Contact the Children's Bureau at (202) 205-7240.
Table 2 lists the files that are to be concatenated, in the proper order, prior to transmission when including the adoption aggregate file. The shaded rows indicate information for the adoption aggregate file and optional information.
Table 2: Adoption Aggregate Data File Format
Order
File Name
Characters per Record
Delimiters
Comments
1
Foster Care Delimiter
1
@
2
Foster Care Summary File
174
One Record per File
3
Foster Care Detailed File
197
One Record per Child
4
Adoption Aggregate Delimiter
1
#
5
Adoption Aggregate File
168
One Record per File
6
Adoption Delimiter
1
$
7
Adoption Summary File
174
One Record per File
8
Adoption Detailed File
111
One Record per Adopted Child
9
Optional Explanatory Footnotes
253
Optional File. Multiple records
B. Data Format Guidelines
Adoption Aggregate Element #2, Report Period Ending Date: It should be in the format of CCYYMM: e.g. 199503.
Adoption Aggregate Elements #3-#22, Age Groups: The numbers in these fields should be right justified and padded with zeros to fill out the field. A field filled with zeros indicates a count of zero. Children are placed in categories based on their age on September 30, 1994. This date is used for the age calculation regardless of the date the file is transferred to ACF.
Shaded areas in Table 3 indicate that the category is not applicable for the given element.
This file contains the total number of records in the detail file and the number of children being reported by age group. Each foster care summary file that is submitted will be compared to a foster care summary file that is generated by the Federal AFCAR system. This will ensure that the data has not been corrupted during transmission or that the file was not properly formatted. If the submitted total number of records does not match the generated total number of records, the foster care detail data file will not be processed. The Data Compliance Summary Report will display the following error message under the heading "Format Errors":
"Foster Care # of Records Not Equal to Summary #
# of Records in summary file: 1321
Actual # of records in file: 1322"
Also, if the number of children being reported by age group does not match the generated number of children, by age group in the detail file, the Data Compliance Summary Report will display the following error message under the heading "Format Errors":
Error Description
Errors
-----------------
------
Inconsistent Summary File: Age 1
X
Inconsistent Summary File: Age 4
X
Inconsistent Summary File: Age 5
X
Inconsistent Summary File: Age 6
X
Below are instructions for creating the Foster Care Summary File:
1. Foster Care Summary File Element #1, Number of Records:
a) Represents the count of the number of records in the detail file. It should NOT be calculated by adding together foster care summary file elements #3-#22 (the calculated ages of children) as these calculations will exclude children with invalid birth dates. Therefore, the total number of records will not necessarily be the same as the sum of the number of children in each age group.
b) Right justify the count and add leading zeros.
2. Foster Care Summary File Element #2, Report Period Ending Date:
a) Insert a valid report period end date for submission. This date should be in the CCYYMM format and it should end with either 09 for September or 03 for March.
3. Foster Care Summary File Elements #3-#22, Age Groups:
a) Validate the child birth dates in the detail file. If a child birth date is either missing or out-of-range (i.e., an invalid date), that record should NOT be included when calculating the age groups. If the file contains invalid birth dates, the total number of records submitted will not be equal to the sum of children in each age group.
b) Definition of the variables used when calculating the count of children in each age group:
Report_date_month. This variable will have a value of either September (09) or March (03), depending upon which of the two semi-annual reporting periods is relevant.
Report_date_year. This variable should be in the CCYY format representing the four-digit century year (e.g. 1996).
Birth_month. This variable is the month of the child's birth.
Birth_year. This variable is the year of the child's birth and should be in the CCYY format representing the four-digit century year (e.g. 1996).
c) Calculate the ages of children in care for each age group using the following decision rule:
d) Note that an age of zero represents children less than one year old.5
e) Right justify the count for each age group and add leading zeros.
B. Foster Care Summary File Format
Table 4 lists the elements of the foster care summary file with the century date format and number of characters. The shaded rows indicate that the category is not applicable for the given element.
As a result of changes to the Office of Management and Budget's Statistical Policy Directive Number 15, Race and Ethnic Standards for Federal Statistics and Administrative Reporting, individuals are now able to select multiple races when responding to questions regarding their race. As required in ACYF-CB-PI-99-01, issued January 27, 1999, and 65 FR 4019, States are to modify their AFCARS to collect and submit race data in the new format, effective October 1, 1999.
If the file is not submitted with the correct record length an error message will be displayed in the data compliance summary report and the file will not be processed by the Federal system. The following error message will appear:
"Incorrect foster care detailed record length. The file contained 182 characters, the correct record length for new race format is 197 characters for foster care."
Table 5 lists the foster care revised detail file format.
Note: Shaded areas indicate that the category is not applicable for the given element.
Table 5: Foster Care Revised Detail File Format
Element
Data Element Description
Century Date Format
Number of Characters
01
State FIPS code
2
02
Report period end date
CCYYMM
6
03
Local Agency FIPS code
5
04
Record number
12
05
Date of most recent periodic review
CCYYMMDD
8
06
Child's date of birth
CCYYMMDD
8
07
Sex
1
08
Race
08a
American Indian or Alaska Native
1
08b
Asian
1
08c
Black or African American
1
08d
Native Hawaiian or Other Pacific Islander
1
08e
White
1
08f
Unable to Determine
1
09
Hispanic or Latino ethnicity
1
10
Disabilities
1
11
Mental retardation
1
12
Visually/hearing impaired
1
13
Physically disabled
1
14
Emotionally disturbed
1
15
Other medical condition requiring special care
1
16
Child ever adopted
1
17
Age at previous adoption
1
18
Date of first removal from home
CCYYMMDD
8
19
Total number of removals
2
20
Date child was discharged from last foster care episode
CCYYMMDD
8
21
Date of latest removal from home
CCYYMMDD
8
22
Removal transaction date
CCYYMMDD
8
23
Date of placement in current foster care setting
CCYYMMDD
8
24
Number of placements
2
25
Removal from home
1
26
Physical abuse
1
27
Sexual abuse
1
28
Neglect
1
29
Alcohol abuse (parent)
1
30
Drug abuse (parent)
1
31
Alcohol abuse (child)
1
32
Drug abuse (child)
1
33
Child disability
1
34
Child's behavior problem
1
35
Death of parent(s)
1
36
Incarceration of parent(s)
1
37
Caretaker's inability to cope
1
38
Abandonment
1
39
Relinquishment
1
40
Inadequate housing
1
41
Current placement setting
1
42
Placement (out of State)
1
43
Case plan goal
1
44
Caretaker family structure
1
45
Year of birth (1st principal caretaker)
CCYY
4
46
Year of birth (2nd principal caretaker)
CCYY
4
47
Date of mother's termination of parental rights
CCYYMMDD
8
48
Date of legal or putative father's termination of parental rights
CCYYMMDD
8
49
Foster family structure
1
50
Year of birth (1st foster caretaker)
CCYY
4
51
Year of birth (2nd foster caretaker)
CCYY
4
52
Race (1st foster caretaker)
52a
American Indian or Alaska Native
1
52b
Asian
1
52c
Black or African American
1
52d
Native American or Other Pacific Islander
1
52e
White
1
52f
Unable to Determine
1
53
Hispanic or Latino ethnicity (1st foster caretaker)
1
54
Race (2nd foster caretaker)
1
54a
American Indian or Alaska Native
1
54b
Asian
1
54c
Black or African American
1
54d
Native Hawaiian or Other Pacific Islander
1
54e
White
1
54f
Unable to Determine
1
55
Hispanic or Latino ethnicity (2nd foster caretaker)
This file contains the total number of records in the detail file and the number of children adopted during the report period by age category. Each adoption summary file that is submitted will be compared to an adoption summary file that is generated by the Federal AFCAR system. This will ensure that there has been no corruption of the data during transmission. If the submitted total number of records does not match the generated total number of records, the adoption detail file will not be validated and the State will incur a penalty for the adoption file. The Data Compliance Summary Report will display the following error message under the heading "Format Errors":
"Adoption # of Records Not Equal to Summary #
# of Records in summary file: 321
Actual # of records in file: 322"
Also, if the number of children being reported by age group does not match the generated number of children, by age group in the detail file, the Data Compliance Summary Report will display the following error message under the heading "Format Errors":
Error Description
Errors
-----------------
------
Inconsistent Summary File: Age 1
X
Inconsistent Summary File: Age 4
X
Inconsistent Summary File: Age 5
X
Inconsistent Summary File: Age 6
X
Therefore, it is important that States create summary files in exactly the same way that the Federal AFCAR System generates summary files.
Below are instructions for creating the Adoption Summary File:
1. Adoption Summary File Element #1, Number of Records:
a) This number should represent the total number of records in the detail file. It should NOT be calculated by adding together adoption summary file elements #3-#22 (the calculated ages of children), as these calculations will exclude children with invalid birth dates. Therefore, the total number of records will not necessarily be the same as the sum of the numbers of children in each age group.
b) Right justify the count and add leading zeros.
2. Adoption Summary File Element #2, Report Period Ending Date:
a) Insert a valid report period end date for submission. This date should be in the CCYYMM format and it should end with either 09 for September or 03 for March.
3. Adoption Summary File Elements #3-#22, Age Groups:
a) Validate the child birth dates in the detail data file. If a child birth date is either missing or out-of-range (i.e., an invalid date), that record should NOT be included when calculating the age groups. If the file contains invalid birth dates, the number of records submitted will not be equal to the sum of children in each age group.
b) Definition of the variables used when calculating the count of children in each age group:
Report_date_month. This variable will have a value of either September (09) or March (03), depending upon which of the two semi-annual reporting periods is relevant.
Report_date_year. This variable should be in the CCYY format representing the four-digit century year (e.g. 1996).
Birth_month. This variable is the month of the child's birth.
Birth_year. This variable is the year of the child's birth and should be in the CCYY format representing the four-digit century year (e.g., 1996).
c) Calculate the ages of adopted children for each age group using the following decision rule:
d) Note that an age of zero represents children less than one year old.6
e) Right justify the count for each age group and add leading zeros.
B. Adoption Summary File Format
Table 6 lists the elements of the adoption summary file with the century date format and number of characters. The shaded rows indicate that the category is not applicable for the given element.
As a result of changes to the Office of Management and Budget's Statistical Policy Directive Number 15, Race and Ethnic Standards for Federal Statistics and Administrative Reporting, individuals are now allowed to select multiple races when responding to questions regarding their race. As required in ACYF-CB-PI-99-01, issued January 27, 1999, and 65 FR 4019, States are to modify their AFCARS to collect and submit race data in the new format, effective October 1, 1999.
If the file is not submitted with the correct record length an error message will be displayed in the data compliance summary report and the file will not be processed by the Federal system. The following error message will appear:
"Incorrect adoption detailed record length. The file contained 96 characters, the correct record length for new race format is 111 characters for adoption."
Table 7 lists the adoption revised detail file format.
Note: Shaded areas indicate that the category is not applicable for the given element.
Table 7: Adoption Detail Revised File Format
Element
Data Element Description
Century Date Format
Number of Characters
01
State FIPS code
2
02
Report period end date
CCYYMM
6
03
Record number
12
04
State agency involvement
1
05
Date of birth of child
CCYYMM
6
06
Sex
1
07
Race
07a
American Indian or Alaska Native
1
07b
Asian
1
07c
Black or African American
1
07d
Native Hawaiian or Other Pacific Islander
1
07e
White
1
07f
Unable to Determine
1
08
Hispanic or Latino ethnicity
1
09
Special needs
1
10
Primary basis for special needs
1
11
Mental retardation
1
12
Visually or hearing impaired
1
13
Physically disabled
1
14
Emotionally disturbed
1
15
Other medically diagnosed condition requiring special care
Create an ASCII data file using the file format listed in Section I above. Be sure all records end with a carriage return/line feed. This is the standard format for DOS ASCII files. Note that the carriage return/line feed will not appear if the file is edited on the PC using a standard editor.
Do not include spaces after the delimiter in the first record. All other records may contain spaces prior to the carriage return/line feed.
Transfer the data file from the PC, as a binary image file, to the mainframe where CONNECT:DIRECT resides. Use either a fixed length or variable length record format. Any LRECL (record length) size is acceptable for either record format.
Recommendation: For efficient use of space use a fixed length record format with the following parameters:
RECFM = FB
LRECL = 255
BLKSIZE = 22950 (must be an even multiple of LRECL)
B. Creating a data file from a mainframe
Create an ASCII data file using the file format listed in Section I above. DO NOT include a carriage return/line feed at the end of each record.
Use either a variable length or fixed length record format.
Recommendation: For efficient use of space use a variable length record format.
If using a variable length record format, be sure to use an LRECL that is 4 bytes longer than the largest record in the data file. The extra 4 bytes are for the RDW (record descriptor word), which gives the length of the record. The following format will accommodate the largest possible footnote:
RECFM = VB
LRECL = 257
BLKSIZE = any large number, e.g., 20000
If using a fixed length record format, be sure to use an LRECL that can accommodate the largest record in the data file. Do not allow extra bytes for an RDW. The following format will accommodate the largest possible footnote:
1. The filename must include the date and time the State is transmitting the AFCARS data file, not the date and time the AFCARS data file(s) were created.
2. The data file should be named according to the type of submission being made:
Regular (R), Subsequent (S), or Test (T).
Regular Data File: An AFCARS State data file for a current report period that is submitted during the appropriate transmission time frame, April 1- May 15 and October 1-November 14.
Subsequent Data File: Submissions that are received after the AFCARS due dates for a regular report (May 15 or November 14) are considered subsequent data files.
Test Data File: An AFCARS data file that is submitted for testing purposes and is not to be used for the Federal AFCARS database. Examples may include: testing the Connect:Direct connection, testing the extraction and transmission process, record format, etc.
3. The State AFCARS data filename is:
VVG1YY9.PAFC.NDM.ss.Zyyyy.Xyymmdd.Thhmm
or
VVG1YY9.PAFC.SIM.ss.Zyyyy.Xyymmdd.Thhmm
Where,
NDM or SIM refers to the mode or type of transmission, with NDM the Connect:Direct (C:D) identifier and, SIM the identifier for Kermit transmissions
ss is the State code Z = report period A or B, yyyy is the report year
X = type of submission R, S, or T
yymmdd is the year, month, and day
hhmm is the hour and minutes the State transmits the data file
Guidelines
The following are general guidelines States should follow when naming the AFCARS data files.
Regular data files should always be concatenated.
If a State modifies and plans to re-transmit only one file for a subsequent transmission, then only that file should be transmitted.
The date and time in the filename must reflect the date and time the files are transmitted.
Examples of Types of Submissions
The following are examples of different submission types.
1. Indiana submits its AFCARS data for the report period ending March 31, 2001 on May l0th at 4:15 p.m. The State must submit a concatenated foster care and adoption data file. The filename would be:
VVG1YY9.PAFC.NDM.IN.A2001.R010510.T1615
2. The State of Connecticut's foster care and adoption data file were compliant with the AFCARS standards for the report period ending September 30, 2001. Connecticut makes corrections to both the adoption and foster care data files and submits a subsequent file on December 10, 2001 at 1:00 p.m. The filename of the resubmitted file would be:
VVG1YY9.PAFC.NDM.CT.B2001.S011210.T1300
3. The State of Maryland has fully implemented its Statewide Automated Child Welfare Information System (SACWIS). In order to check the extraction routine, the staff want to submit an AFCARS test data file. The State extracts the first full report period from the SACWIS, which is the report period ending September 30, 2002. The test file is submitted December 10, 2002 at 1:00 p.m. The filename would be:
VVG1YY9.PAFC.NDM.MD.B2002.T021210.T1300
If further information is required please contact the Children's Bureau at (202) 205-7240.
1 Initial issuance date July 6, 1996. Back 2 Revised May 6, 1996 for century date format, see ACYF-IM-CB-96-08, issued April 7, 1996. See also 65 FR 4019. Back 3 Revised for race categories, see ACYF-CB-PI-99-01, issued January 27, 1999. See also 65 FR 4019. Back 4 Revised for file name May 2001. Back 5 If the birth date has been entered as a date that is AFTER the end of the report period but is still a valid date, the Federal system will calculate a negative age for that child. If this occurs, this record should be included in the "under 1 year old" age group. Back 6 If the birth date has been entered as a date that is AFTER the end of the report period but is still a valid date, the Federal system will calculate a negative age for that child. If this occurs, this record should be included in the "under 1 year old" age group. Back 7 This total does not include the carriage return/line feed. Back
Please send all comments and questions on this subject to Questions.
Please send all comments and Web feedback to Feedback.