Home > Laws & Policies > Policy/Program Issuances > Program Instructions > PI-99-01

 

Technical Bulletin #2
AFCARS File Format

REVISED:   1/27/1999^1,2

This technical bulletin supplements information in 45 CFR 1355.40 on 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.

Section I	General File Format Information: the data file order, format, and general guidelines for transmission.
Section II	Century Date Format: four digit century date.
Section III	Adoption Aggregate File Format: the format for the one-time-only submission of the Adoption Aggregate File.
Section IV	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.
Section V	Foster Care Revised Detail File Format: the revised format for the foster care detail file.
Section VI	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.
Section VII	Adoption Revised Detail File Format: the revised format for the adoption detail file.
Section VIII	Optional Explanatory Footnote File Format: the format for the optional explanatory footnote file.
Section IX	File Creation.

_________________

¹Revised for century date format 5/6/1996, see ACYF-IM-CB-96-08, issued 4/17/1996
²Revised format for race categories, see ACYF-CB-PI-99-01, January 27, 1999

I. General File Format Information

A. File Order

Below is a table with the files which are to be concatenated in the proper order prior to transmission:

Order	File Name	Characters per Record	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 Adoption.
9	Optional Explanatory Footnotes	253	Optional File. Multiple records.

B. Format Guidelines

• The data file which is transmitted must be in ASCII. This format is specified in Appendix C of 45 CFR 1355. EBCDIC files will not be processed.

• A Detail File must always be accompanied by a Summary File. This applies to both Adoption and Foster Care.

• 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.

• Record Number field: The record number (Foster Care Detail Submission File Element 4 and Adoption Detail Submission File Element 3) should be right justified and padded with zeros 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 Detail Submission File Element 36 (Monthly amount of subsidy). If data element 35 is no, then 00000 means no subsidy amount. If 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 standards: For detailed information on format standards of individual elements, see Technical Bulletins #7 and #8.

II.Century Date Formats

AFCARS requires all date formats to accommodate century information -- either a YYYYMMDD, YYYYMM or YYYY, where YYYY represents the four digit century year, e.g. 1996. States are required to use the 4 digit format, regardless of the reporting period, beginning October 1, 1996 (ACYF-IM-CB-96-08).

The following table lists both the foster care and the adoption data elements which should have the century date format.

Element Number	Data Element Name	Century Date Format
Foster Care Summary File
02	Report period ending date	YYYYMM
Foster Care Detail File
02	Report period ending date	YYYYMM
05	Date of most recent periodic review	YYYYMMDD
06	Child's date of birth	YYYYMMDD
18	Date of first removal from home	YYYYMMDD
20	Date child was discharged from last foster care episode	YYYYMMDD
21	Date of latest removal from home	YYYYMMDD
22	Removal transaction date	YYYYMMDD
23	Date of placement in current foster care setting	YYYYMMDD
45	Year of birth (1st principal caretaker)	YYYY
46	Year of birth (2nd principal caretaker)	YYYY
47	Date of mother's parental rights termination	YYYYMMDD
48	Date of legal or putative father's parental rights termination	YYYYMMDD
50	Year of birth (1st foster caretaker)	YYYY
51	Year of birth (2nd foster caretaker)	YYYY
56	Date of discharge from foster care	YYYYMMDD
57	Foster care discharge transaction date	YYYYMMDD
Adoption Aggregate File
02	Report period ending date	YYYYMM
Adoption Summary File
02	Report period ending date	YYYYMM
Adoption Detail File
02	Report period ending date	YYYYMM
05	Date of birth of child	YYYYMM
16	Mother's year of birth	YYYY
17	Father's (putative or legal) year of birth	YYYY
19	Date of mother's termination of parental rights	YYYYMMDD
20	Date of father's termination of parental rights	YYYYMMDD
21	Date adoption legalized	YYYYMMDD
23	Mother's year of birth	YYYY
24	Father's year of birth	YYYY
Optional Explanatory Footnote File
03	Report period end date	YYYYMM

III. Adoption Aggregate Data File Format

A. File Description

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 only to be reported one time and is to be included with the first reporting period (October 1, 1994 - March 31, 1995) data submission. States are required (under §1355.40 (a)(3)) of the AFCARS final rule) to submit this data although no penalty is prescribed. If the State is 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 (data elements 2-21).

B. Format Guidelines

• Element 2, Report Period Ending Date: The date in this field should be March 1995, regardless of the actual date the file is transferred to ACF. It should be in the format of YYYYMM: 199503.
• 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.

Element Number	Data Element Description	Century Date Format	Number of characters
01	State FIPS code		2
02	Report period ending date	YYYYMM	6
03	Children under age 1 receiving subsidies		8
04	Children age 1 receiving subsidies		8
05	Children age 2 receiving subsidies		8
06	Children age 3 receiving subsidies		8
07	Children age 4 receiving subsidies		8
08	Children age 5 receiving subsidies		8
09	Children age 6 receiving subsidies		8
10	Children age 7 receiving subsidies		8
11	Children age 8 receiving subsidies		8
12	Children age 9 receiving subsidies		8
13	Children age 10 receiving subsidies		8
14	Children age 11 receiving subsidies		8
15	Children age 12 receiving subsidies		8
16	Children age 13 receiving subsidies		8
17	Children age 14 receiving subsidies		8
18	Children age 15 receiving subsidies		8
19	Children age 16 receiving subsidies		8
20	Children age 17 receiving subsidies		8
21	Children age 18 receiving subsidies		8
22	Children over age 18 receiving subsidies		8
Total Characters			168

IV. Foster Care Summary File

A. Foster Care Summary File Format

Element	Data Element Description	Century Date Format	Number of characters
01	Number of records		8
02	Report period ending date	YYYYMM	6
03	Children in care who are under age 1		8
04	Children in care who are age 1		8
05	Children in care who are age 2		8
06	Children in care who are age 3		8
07	Children in care who are age 4		8
08	Children in care who are age 5		8
09	Children in care who are age 6		8
10	Children in care who are age 7		8
11	Children in care who are age 8		8
12	Children in care who are age 9		8
13	Children in care who are age 10		8
14	Children in care who are age 11		8
15	Children in care who are age 12		8
16	Children in care who are age 13		8
17	Children in care who are age 14		8
18	Children in care who are age 15		8
19	Children in care who are age 16		8
20	Children in care who are age 17		8
21	Children in care who are age 18		8
22	Children in care who are over age 18		8
Total Characters			174

B. Generating the Foster Care Summary File

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. If the submitted file does not match the generated file, the foster care detail data file will not be validated and the State will incur a penalty for the foster care file. Therefore, it is important that States create summary files in exactly the same way that the Federal AFCAR System generates summary files. The Foster Care Summary file will NOT be used for analysis.

Below are directions for creating the Foster Care Summary File:

• Element 1, Number of Records:

  1. Represents the count of the number of records in the detail file. This number should represent the total number of records in the detail file. It should NOT be calculated by adding together elements 3-22 (the calculated ages of children) as these calculations will exclude children with invalid birth dates. Therefore, element 1 will not necessarily be the same as the sum of all the numbers of children in each age group. (See Elements 3-22 below.)

  2. Right justify the count and add leading zeros.

• Element 2, Report Period Ending Date:

  3. Insert a valid report period end date for submission. This date should be in the YYYYMM format and it should end with either 09 for September or 03 for March.

• Elements 3-22, Age Groups:

  4. 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.

  5. 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 YYYY 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 YYYY format representing the four digit century year, e.g. 1996.

  6. Calculate the ages of children in care for each age group using the following decision rule:

If Birth_month > Report_date_month	---> No	age = (Report_date_year - Birth_year)
| | Yes v
age = (Report_date_year - Birth_year) -1

 

7. Note that an age of zero represents children less than one year old.

8. Right justify the count for each age group and add leading zeros.

V. Foster Care Revised Detail File Format

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, States are to modify their AFCARS to collect and submit race data in the new format, effective October 1, 1999. Below is the table with the foster care revised detail file format.

Data Element Description	Century Date Format	Number of characters	Element
01	State FIPS code		2
02	Report period end date	YYYYMM	6
03	Local Agency FIPS code		5
04	Record number		12
05	Date of most recent periodic review	YYYYMMDD	8
06	Child's date of birth	YYYYMMDD	8
07	Sex		1
08	Race
08a	American Indian or Alaskan 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 origin		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		1
16	Child ever adopted		1
17	Age at previous adoption		1
18	Date of first removal from home	YYYYMMDD	8
19	Total number of removals		2
20	Date child was discharged from last foster care episode	YYYYMMDD	8
21	Date of latest removal from home	YYYYMMDD	8
22	Removal transaction date	YYYYMMDD	8
23	Date of placement in current foster care setting	YYYYMMDD	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)	YYYY	4
46	Year of birth (2nd principal caretaker)	YYYY	4
47	Date of mother's termination of parental rights	YYYYMMDD	8
48	Date of legal or putative father's termination of parental rights	YYYYMMDD	8
49	Foster family structure		1
50	Year of birth (1st foster caretaker)	YYYY	4
51	Year of birth (2nd foster caretaker)	YYYY	4
52	Race (1st foster caretaker)
52a	American Indian or Alaskan 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 origin (1st foster caretaker)		1
54	Race (2nd foster caretaker)
54a	American Indian or Alaskan 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 origin (2nd foster caretaker)		1
56	Date of discharge from foster care	YYYYMMDD	8
57	Foster care discharge transaction date	YYYYMMDD	8
58	Reason for discharge		1
59	Title IV-E (foster care)		1
60	Title IV-E (adoption assistance)		1
61	Title IV-A		1
62	Title IV-D		1
63	Title XIX		1
64	SSI or other benefits		1
65	None of the above source of federal support		1
66	Amount of monthly subsidy		5
Total Characters			197

VI. Adoption Summary File

A. Adoption Summary File Format

Element	Data Element Description	Century Date Format	Number of characters
01	Number of records		8
02	Report period ending date	YYYYMM	6
03	Children adopted who are under age 1		8
04	Children adopted who are age 1		8
05	Children adopted who are age 2		8
06	Children adopted who are age 3		8
07	Children adopted who are age 4		8
08	Children adopted who are age 5		8
09	Children adopted who are age 6		8
10	Children adopted who are age 7		8
11	Children adopted who are age 8		8
12	Children adopted who are age 9		8
13	Children adopted who are age 10		8
14	Children adopted who are age 11		8
15	Children adopted who are age 12		8
16	Children adopted who are age 13		8
17	Children adopted who are age 14		8
18	Children adopted who are age 15		8
19	Children adopted who are age 16		8
20	Children adopted who are age 17		8
21	Children adopted who are age 18		8
22	Children adopted who are over age 18		8
Total Characters			174

B. Generating the Adoption Summary File

Below are the steps to follow when creating your adoption summary file. 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 file does not match the generated file, the adoption detail file will not be validated and the State will incur a penalty for the adoption file. Therefore, it is important that States create summary files in exactly the same way that the Federal AFCAR System generates summary files.

These files will NOT be used for analysis.

Below are directions for creating the Adoption Summary File:

• Element 1, Number of Records:

  • Count of the number of records in the detail file. This number should represent the total number of records in the detail file. It should NOT be calculated by adding together elements 3-22 (the calculated ages of children), as these calculations will exclude children with invalid birth dates. Therefore, the value for element 1 will not necessarily be the same as the sum of the numbers of children in each age group. (See Elements 3-22 below.)

  • Right justify the count and add leading zeros.

• Element 2, Report Period Ending Date:

3. Insert a valid report period end date for submission. This date should be in the YYYYMM format and it should end with either 09 for September or 03 for March.

   • Elements 3-22, Age Groups:

4. 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.

5. 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 YYYY 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 YYYY format representing the four digit century year, e.g., 1996.

6. Calculate the ages of adopted children in each age group using the following decision rule:

If Birth_month > Report_date_month	---> No	age = (Report_date_year - Birth_year)
| | Yes v
age = (Report_date_year - Birth_year) - 1

7. Note that an age of zero represents children less than one year old.

8. Right justify the count for each age group and add leading zeros.

 

VII. Adoption Detail Revised File Format

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, States are to modify their AFCARS to collect and submit race data in the new format, effective 10/1/1999. Below is the table with the adoption revised detail file format.

 

Element	Data Element Description			Century Date Format	Number of characters
01	State FIPS code				2
02	Report period end date			YYYYMM	6
03	Record number				12
04	State agency involvement				1
05	Date of birth of child			YYYYMM	6
06	Sex				1
07	Race
07a	American Indian or Alaskan 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 origin				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				1
16	Mother's year of birth			YYYY	4
17	Father's (putative or legal) year of birth			YYYY	4
18	Mother married				1
19	Date of mother's termination of parental rights			YYYYMMDD	8
20	Date of father's termination of parental rights			YYYYMMDD	8
21	Date adoption legalized			YYYYMMDD	8
22	Adoptive parents family structure				1
23	Mother's year of birth			YYYY	4
24	Father's year of birth			YYYY	4
25	Adoptive mother's race
25a	American Indian or Alaskan Native				1
25b	Asian				1
25c	Black or African American				1
25d	Native Hawaiian or Other Pacific Islander				1
25e	White				1
25f	Unable to Determine				1
26	Hispanic or Latino origin mother				1
27	Adoptive father's race
27a	American Indian or Alaskan Native				1
27b	Asian				1
27c	Black or African American				1
27d	Native Hawaiian or Other Pacific Islander				1
27e	White				1
27f	Unable to Determine				1
28	Hispanic or Latino origin father				1
29	Stepparent				1
30	Other relative				1
31	Foster parent				1
32	Other non-relative				1
33	Child was placed from				1
34	Child was placed by				1
35	Child receiving a monthly subsidy				1
36	Monthly amount				5
37	Title IV-E adoption assistance				1
Total Characters					111

 

VIII. Optional Explanatory Footnote File Format

Below is the table with the optional explanatory footnote file format.

Element Number	Data Element Description	Century Date Format	Number of characters
01	Header ("%%")		2
02	State FIPS code		2
03	Report period end date	YYYYMM	6
04	File reference		1
05	Data element reference		2
06	Footnote text		240
Total Characters			2535

 

See Bulletin #3 for instructions on creating optional explanatory footnotes.

________________
⁵This total does not include the carriage return/line feed.

IX. AFCARS File Creation

A. Creating a transmission file from a PC

1. Create an ASCII transmission 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 you edit the file on the PC using a standard editor.

2. Do not include spaces after the delimiter in the first record. All other records may contain spaces prior to the carriage return/line feed.

3. Transfer the transmission 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 we encourage using 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 transmission file from a mainframe

1. Create an ASCII transmission file using the file format listed in Section I above. DO NOT include a carriage return/line feed at the end of each record.

2. Use either a variable length or fixed length record format.

Recommendation: For efficient use of space we encourage using a variable length record format.

1. If using a variable length record format, be sure to use an LRECL that is 4 bytes longer than the largest record in the transmission 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

2. If using a fixed length record format, be sure to use an LRECL that can accommodate the largest record in the transmission file. Do not allow extra bytes for an RDW. The following format will accommodate thelargest possible footnote:

   RECFM = FB
   LRECL = 253