Pesticide Sales and Use Reporting Data Dictionary

The file readme.txt included with the downloadable reports describes the record layout for each of the eight report files (referred to here as data files). This data dictionary is meant to supplement the readme.txt and other information provided by NYSDEC and Cornell University. Since the same data elements are returned by the on line searches, you may also use this dictionary for those data sets.

The table below shows which data elements are in each data file. The data elements appear in the order listed in this table.

Data File	Data Element (The number indicates the column in the data file that contains the data element.)
Data File	EPA Registration Number *	AI ¶ Code	Product Name	AI Name	Quantity: Volume	Quantity: Weight	AI Quantity	County	Zip Code *
prodnames ‡	1		2
sales_all	1				2	3
sales_cnty	2				3	4		1
sales_zip †	2				3	4			1
sales_euse	1				2	3
sales_resale	1				2	3
use_all	1				2	3
use_cnty	2				3	4		1
use_zip †	2				3	4			1
ai_names §	1		2
ai_sales_all		1		2			3
ai_sales_cnty		2		3			4	1
ai_sales_zip †		2		3			4		1
ai_sales_euse		1		2			3
ai_sales_resale		1		2			3
ai_use_all		1		2			3
ai_use_cnty		2		3			4	1
ai_use_zip †		2		3			4		1

In Excel the format of this column needs to be changed to text.

†

In Excel versions prior to 2007, if you get a warning that "File was not loaded completely," do not use the file. This will occur with use_zip because it has more rows than Excel can handle. You must use another tool such as Access to load use_zip.

‡

This file is a reference file for the product report files. The EPA Registration Number column may be used to relate the records in each file.

This file is a reference file for the active ingredient report files. The Active Ingredient Code column may be used to relate the records in each file.

AI is the abbreviation for active ingredient.

EXAMPLE: The data file sales_all has three data elements (EPA Registration Number, volume, and weight). The EPA Registration Number is in column 1, the total quantity sold in gallons (volume) is in column 2, and the total quantity sold in pounds (weight) is in column 3. Column 1, the Registration Number, needs to be stored as text. The product names associated with each record may be accessed in the prodnames file using column 1 as the linking field.

Description of Data Elements

EPA Registration Number

Description	This is the number assigned by the U.S. EPA to every pesticide product. It is reported as it appears on the product label.
Data Files	sales_all.tab, use_all.tab, sales_enduse.tab, sales_resale.tab, sales_cnty.tab, use_cnty.tab, sales_zip.tab, use_zip.tab, prodnames.tab
Format	Text - comprised of three sets of numbers separated by hyphens (for example, 4-99-10404). The first number is the EPA Company Number (i.e., the manufacturer of the product); the second is the EPA Product Number; the third, which is not always provided, is the EPA Distributor Number.
Values	Valid EPA Registration Numbers Additional values: Invalid EPA No.—does not match those EPA Registration numbers for pesticide products registered in NY state or ever registered in NY state as of the reporting year. Since the 2004 reporting year, EPA Registration numbers are also validated against the EPA's data and only marked as invalid if not found in that data. Unreported EPA No. Illegible EPA No. Irregular EPA No.—usually refers to source pesticide reports where two EPA Registration numbers were submitted for a single record.
Comments	Use the New York State Bureau of Pest Management Information Portal (NYSPAD) to: Identify the pesticide product that you are interested in Determine which pesticides are listed in the data file Learn about the characteristics of a specific product View the NYS product label View chemical information

Active Ingredient Code

Description	The chemical code assigned by the EPA to the active ingredient.
Data File	ai_sales_all.tab, ai_use_all.tab, ai_sales_enduse.tab, ai_sales_resale.tab, ai_sales_cnty.tab, ai_use_cnty.tab, ai_sales_zip.tab, ai_use_zip.tab
Format	Numeric - 6 digit, zero-filled number
Values	000001 to 999999
Comments	The active ingredient codes are sourced from the EPA's Pesticide Product Information (PPIS) system. For more information, see the PPIS data dictionary. The EPA assigned pesticide chemical code is also commonly referred to as the Shaughnessy code or chemical code.

Product Name

Description	The product name as it appears on the product label.
Data File	prodnames.tab
Format	Text
Values	Not applicable
Comments	The product name is not the active ingredient. The data file prodnames.tab can be used to look up product names using the EPA Registration Number. The file has one record for each product name for each EPA registration number in our system.

Active Ingredient Name

Description	The name of the active ingredient
Data File	ai_names.tab
Format	Text
Values	Not applicable
Comments	The active ingredient names are sourced from the EPA's Pesticide Product Information (PPIS) system. While each active ingredient may have multiple names, our data lists one name per active ingredient. Common names, synonyms, scientific, and preferred names are used. The specific name types used in our files are (listed in descending order of frequency of occurrence): Other Common, ISO, ANSI, Other Systematic, Trade, CAS 9CI, CAS 7CI and IUPAC. For more information, see the PPIS data dictionary. The active ingredient(s) in a pesticide may be determined from the product label available in NYSPAD or by looking at the product ingredient information returned for NYSPAD searches. The data file ai_names.tab can be used to look up the active ingredient name using the active ingredient code. The file has one record for each active ingredient code.

Quantity - Volume

Description	The total amount (in gallons) of a liquid pesticide product sold or used.
Data Files	sales_all.tab, use_all.tab, sales_enduse.tab, sales_resale.tab, sales_cnty.tab, use_cnty.tab, sales_zip.tab, use_zip.tab
Format	Numeric
Values	The range of values depends on the amount of product reported for the year.
Comments	For the pesticide use data, this is the total amount of product used "out of the manufacturer's container." For the data on sales to certified private applicators reports, the quantity reported is the amount of product purchased and intended for use. For the data on sales of restricted use pesticides for resale and end use reports, the quantity reported is the container size and number of containers sold. The total amount is computed and converted to gallons by NYSDEC. This is not the amount of the active ingredient in the product. For some products, there is a value for both volume and weight. The unit of the product is sometimes reported incorrectly; that is, volume is reported when the pesticide is measured in weight and visa versa. Only the product label (available through NYSPAD) tells you if a product has a solid or liquid formulation.

Quantity - Weight

Description	The total amount (in pounds) of a solid pesticide product sold or used reported in pounds.
Data Files	sales_all.tab, use_all.tab, sales_enduse.tab, sales_resale.tab, sales_cnty.tab, use_cnty.tab, sales_zip.tab, use_zip.tab
Format	Numeric
Values	The range of values depends on the amount of product reported for the year.
Comments	For the pesticide use data, this is the total amount of product used "out of the manufacturer's container." For the data on sales to certified private applicators reports, the quantity reported is the amount of product purchased and intended for use. For the data on sales of restricted use pesticides for resale and end use reports, the quantity reported is the container size and number of containers sold. The total amount is computed and converted to pounds by NYSDEC. This is not the amount of the active ingredient in the product. For some products, there is a value for both volume and weight. The unit of the product is sometimes reported incorrectly; that is, weight is reported when the pesticide is measured by volume. Only the product label (available through NYSPAD) tells you if a product has a solid or liquid formulation.

Active Ingredient Quantity

Description	The total amount (in pounds) of active ingredient sold or used.
Data Files	ai_sales_all.tab, ai_use_all.tab, ai_sales_enduse.tab, ai_sales_resale.tab, ai_sales_cnty.tab, ai_use_cnty.tab, ai_sales_zip.tab, ai_use_zip.tab
Format	Numeric
Values	The range of values depends on the percentage of active ingredient in the products reported for the year.
Comments	This field holds the amount of active ingredient contained in the products reported on the sales and use reports.

County

Description	For sales, the name of the New York county where the buyer intends to use the pesticide. For use, the name of the county where the pesticide was applied.
Data Files	sales_cnty.tab, use_cnty.tab
Format	Text
Values	Valid county names Additional values: Invalid County—county does not exist in NY State. Illegible County Unreported County Irregular County—usually refers to source pesticide reports where two counties were submitted for a single record.
Comments

Zip Code

Description	For sales, the New York State ZIP code where the buyer intends to use the pesticide. For use, the ZIP code where the pesticide was applied.
Data Files	sales_zip.tab, use_zip.tab
Format	Text
Values	Valid 5-digit ZIP codes Additional values: Invalid Zip Code—does not exist in NY State. Illegible Zip Code Unreported Zip Code Irregular Zip Code—usually refers to source pesticide reports where two ZIP Codes were submitted for a single record. Private—ZIP codes unique to a specific location/organization. The Pesticide Reporting Law does not allow identification of the locations of specific applications. The data for ZIP codes associated with specific organizations or locations have been reported by NYSDEC using one of two methods. If the ZIP code had an enclosing ZIP code, the data have been reported in the enclosing ZIP code. Otherwise the data have been reported under the "Private" ZIP code.
Comments	Because of the size of the ZIP code files, they cannot be imported into Excel. A database management program (such as Access) or statistical analysis program (such as SAS) can be used to work with this file.