State, Local, or Tribal Government (WIC Directors and Database Administrators)

Appendix F.1

Instruction Sheet for Submitting EBT Transaction Data Files

OMB Number: 0584-XXXX

Expiration Date: XX/XX/XXXX

Instruction Sheet for Submitting

EBT Transaction Data Files

What is the timeline for each data submission?

Data should be submitted twice: in October 2017 and in March 2018.

What should be included?

We are asking for two submissions, each which longitudinal data for specified time periods.

• October 2017 file submission to include EBT activity during the period May 1, 2016 to August 30, 2017

• March 2018 file submission to include EBT activity during the period September 1, 2017 to January 31, 2018.

Each data submissions should contain these files:

• Benefit issuance – Records for all benefits issued during the time period.

• Benefit redemption history – Household records in the redemption history during the time period. Please include all records (purchases, refunds, voids).

• Vendor list – List of all vendors authorized at any time during the time period.

• Food list - List of all foods approved during the time period.

• Documentation of State-specific codes – As specified in the tables below.

What is the preferred file format?

Please provide files in SAS or ASCII (text) format; smaller data files (such as the vendor list, food list, and State-specific code documentation) may be submitted in MS Excel. Other formats may be acceptable; please discuss alternate formats with Insight. If providing an ASCII file, please use a pipe delimiter and include the names of data elements in a header row, or specify the columns in which each variable is located.

What variables should be included?

Tables A-D provide the list of the variables to include in each of the four files listed above. Please provide the data elements in the order listed in Tables A-D, or with data element names that have a clear correspondence to the names in Tables A-D.

How should missing data be indicated?

Missing values should be indicated by a BLANK space. Do NOT fill unknown values with zeros. Zero should ONLY indicate an actual zero value.

According to the Paperwork Reduction Act of 1995, an agency may not conduct or sponsor, and a person is not required to respond to, a collection of information unless it displays a valid OMB control number. The valid OMB control number for this information collection is 0584-XXXX. The time required to complete this information collection is estimated to average 120 minutes per response, including the time for reviewing instructions, searching existing data sources, gathering and maintaining the data needed, and completing and reviewing the collection of information.

How will Insight ensure privacy of State data?

Personally identifiable information will be available only to project staff with a need to know either for the purposes of data collection or data editing. The analysis will be performed on secondary files from which the identifying data have been deleted, and no identifiers will be disclosed in reports. Results will be reported in aggregate form. Insight will provide assurances to State agencies that data which could be used to identify individual households will be purged from their systems at the conclusion of the study. 

How should the files be submitted?

To protect the data, please submit files using Insight’s FTP system, a secure file transfer site that encrypts both commands and data, preventing passwords and sensitive information from being accessed during transmission. The URL for the FPT site is <insert FPT site here>. Your password will be sent separately.

Questions or concerns?

If you have any questions or concerns, contact Carole Trippe at [email protected] or 703-504-9498.

Table A. Benefit Issuance

This data file should have one record per food category or subcategory per benefit issuance.

Data Item Number	Data Item	Definition
1.	State Agency ID	The first 7 digits of the 10-digit identification code used in the WIC Local Agency Directory (LAD) maintained by FNS
2.	Household ID	Household ID. Use the same ID as provided in the administrative data file
3.	Card Number	A series of digits appearing on the face of the WIC Card or encoded on the magnetic stripe of a card or assigned to a Smart Card.
4.	Benefit Issuance ID	A unique number identifying a benefit issuance; assigned by the WIC MIS system.
5.	Benefit Begin Date	First date on which benefits may be used (MMDDYYYY format)
6.	Benefit End Date	Last date on which benefits may be used (MMDDYYYY format)
7.	Benefit Quantity Issued	The quantity of a food item issued
8.	Benefit Quantity Expired	Optional Item The quantity of a food item that have passed the last day to spend
9.	Benefit Unit Description	Text description of the originator’s specified benefit issuance unit of measure, e.g., can , pkg, jar, ounce
10.	Category Code	A code identifying the type of product. Please use your own codes and provide documentation.
11.	Subcategory Code	A code further identifying the type of product within a Category code. Please use your own codes and provide documentation.

Table B. Benefit Redemption

This data file should have one record per food item (UPC/PLU) per transaction.

Data Item Number	Data Item	Definition
1.	State Agency ID	The first 7 digits of the 10-digit identification code used in the WIC Local Agency Directory (LAD) maintained by FNS
2.	Household ID	Household ID. Use the same ID as provided in the administrative data file
3.	Card Number	A series of digits appearing on the face of the WIC Card or encoded on the magnetic stripe of a card or assigned to a Smart Card.
4.	Benefit Issuance ID	A unique number identifying a benefit issuance; assigned by the WIC MIS system.
5.	Benefit Begin Date	First date on which benefits may be used (MMDDYYYY format)
6.	Benefit End Date	Last date on which benefits may be used (MMDDYYYY format)
7.	Transaction Type Code	Identifies specific transaction types (Purchase, Void, Expunged/expired, etc.). Please use your own codes and provide documentation.
8.	Original Unique EBT Transaction Identifier	For a reversal, refers to the original transaction
9.	WIC Vendor ID	Value assigned to identify the WIC Vendor. Please use your own codes and provide documentation..
10.	Transaction Date	Date of the transaction (MMDDYYYY format)
11.	Transaction Time	Time of the transaction (HHMM, 24 hour notation)
12.	Category Code	Please use your own codes and provide documentation.
13.	Subcategory Code	Please use your own codes and provide documentation.
14.	Original Requested Price	Original requested amount. The purchase amount requested by the vendor for the transaction. For two 18 ounce boxes of cereal, this is the price requested by the vendor for both boxes.
15.	Final Price Paid to Vendor	The final amount paid by WIC to the vendor after NTE limits, recoupment policies, and any other price reductions have been applied. For two 18 ounce boxes of cereal, this is the amount actually provided to the vendor for both boxes.
16.	Purchase Quantity	The number of containers of a food item being purchased. For two 18-ounce boxes of cereal, this should be “2.”
17.	UPC/PLU	The UPC or PLU assigned to an item.

Table C. Vendor List

This data file should have one record per vendor.

Data Item Number	Data Item	Definition
1.	WIC Vendor ID	Value assigned to identify the WIC Vendor. Please use your own codes.
2.	WIC VendorBusiness Name	Business name of the WIC Vendor.
3.	Address line 1	Physical address, line 1
4.	Address line 2	Physical address, line 2
5.	Address city	Physical address, city
6.	Address state	Physical address, state
7.	Address ZIP code	Physical address, ZIP code
8.	Vendor Type Code	WIC vendor type (Chain, Independent grocer, Pharmacy, etc.). Please use your own codes and provide code definitions.
9.	WIC Vendor Peer Group ID	An identifier assigned to a set of vendors defined by the WIC State Agency for managing cost containment. Please use your own codes and provide code definitions.

Table D. WIC Approved Foods

This data file should have one record per approved food.

Data Item Number	Data Item	Definition
1.	UPC/PLU	The UPC or PLU assigned to an item.
2.	Date, Begin UPC/PLU Data	The date for which the UPC/PLU data shall become available
3.	Date, End UPC/PLU Data	The last date for which the UPC/PLU data shall be used
4.	Category Code	Please use your own codes and provide documentation.
5.	Subcategory Code	Please use your own codes and provide documentation.
6.	Food Item Description	Name for the WIC food item
7.	Brand Name	The brand of the food item, if available.
8.	Package Size	The size of the container. For an 18 ounce box of cereal, this should be “18.” May be left blank for fresh fruits and vegetables with a PLU instead of a UPC.
9.	Unit of Measure	The unit of measure of the container. For an 18 ounce box of cereal, this should be “ounce.” May be left blank for fresh fruits and vegetables with a PLU instead of a UPC.
The following data elements are repeated for each UPC/PLU data element where an NTE price is provided:
10.	NTE Price	Not to exceed price. The maximum allowed price for a food item as established by the WIC State Agency for the Vendor peer group.
11.	WIC Vendor Peer Group ID	An identifier assigned to a set of vendors defined by the WIC State Agency for managing cost containment. Please use your own codes and provide code definitions.

As indicated in the tables above, please provide code definitions for the following in an Excel file or ASCII text file:

1. Category Code – Identifies the type of product.

2. Subcategory Code – Further identifies the type of product within a Category Code.

3. Benefit Transaction Type Code – Identifies specific transaction types (Purchase, Void, Expunged/expired, etc.).

4. Vendor Type Code - WIC vendor type (Chain, Independent grocer, Pharmacy, etc.).

(5) WIC Vendor Peer Group ID – Identifies a set of vendors defined by the WIC State Agency for managing cost containment.