Title

finddata - find matching values in an external file; optionally copy data

Syntax

finddata findvars using path_el [if] [in] [, options]

Where findvars is a varelist that can also include:

varname_c=varname_e

Where:

varname_c is a varname in the current data

varname_e is the varname of a corresponding variable in the external file

copy(copyvars) copy variables into the current data from the external file

complement add records from the external file that were not found

distinct use only distinct records from the external file

expand expand current records to pair with all found records

onefind flag an error if more than one current record finds the same external record

noreplace flag an error if an existing variable would be replaced

picky use the exact values of string findvars

pass() pass along import parameters

Description

finddata finds records in an external file, based on matching findvars. Where a variable is named differently in the current data and the external file, use

varname_c=varname_e

When a findvar is string in one place, and numeric in the other, the string will be converted to a number before matching.
If the string contains non-numeric characters, it will cause an error.

finddata creates a variable named _found in the current data, replacing any previous versions. It holds, for each record in the current data, the number of records found in the external file.

o- Records excluded by if/in will have _found=.e

o- Records added by complement will have _found=.c

o- Records expanded will have duplicate values of _found.

finddata also leaves three return values:

o- r(complement) holds the number of records in the external file that were not found.

o- r(added) holds the names of variables added to the current data.

o- r(replaced) holds the names of variables replaced by those from the external file.

Options

copy(copyvars) specifies variables to copy into the current data from the external file, and optionally their names in the current data.
copyvars is a varelist that can also include:

varname_e->varname_c

to copy in (external) varname_e as (current data) varname_c.

[+] When a variable is copied in under the same name as an existing variable, the existing variable is replaced with the external variable.

distinct filters the external file so that only distinct records are considered. That is, just one record for every combination of findvars and copyvars.

The filtering happens before comparisons with the current data, so distinct affects all record counts, including those in _found, complement, expand, and onefind.

complement adds records from the external file that did not match on findvars.

Note that the return value r(complement) holds the count of unmatched records, whether or not they are added.

expand allows one record in the current data to find more than one external record. Each current record will be expanded so that it can be paired with each external record.
If expand is not specified, any _found>1 will cause an error.

onefind requires that only one current record may find the same external record.

noreplace will force an error if an existing variable would be replaced by external data.

picky affects string matching. Ordinarily, finddata ignores leading and trailing spaces, multiple internal spaces, and uppler/lower case. picky specifies matching on all of those things.

pass() passes import/export options along to the appropriate handler.

o- For file extensions .txt or .csv, the options are those for import delimited.

o- For file extensions .xl, .xls or .xlsx, the options are described under portel xl.

o- For other file extensions (besides .dta), the options are those for callst.