Efficient Sample selection

With some thought, and some lesser know options to the -merge- command it should be possible to dispense with the SAS step for nearly all work, as long as the final analysis dataset will fit in core. With memory allocations of up to 40 GB now readily available that would cover 40 million observations on 1,000 float variables in the analysis subsample and no limit on the size of the full file from which the sample is drawn.

In particular, there is no need for the sample selection and analysis variables to be in core at the same time.

Row and column selection with -merge-

In order to minimize the amount of memory used, we need to separate the sample selection decision from the variable inclusion decision. We assume that the initial ("full") .dta file will not fit in core, but has been created by Stat/Transfer, or on a larger computer or is in a fixed format. We also assume a record id is present and that the data are sorted by that value. To get such a record id we can record the value of _n when the full file is created. The procedure is as follows:

1. List only the variables needed in sample selection in the -use- command with the -using- keyword. Use the -if- qualifier to subset records, to the extent they do not have cross-observation restrictions.

   use varlist1... if in_sample_condition using filename

2. If the data is read via a Stata dictionary, list only the variables necessary for sample selection in the dictionary, and use the -if- qualifier to the -infile- command.
3. Apply any cross-observation qualifications to identify the subsample required for analysis.
4. Drop observations not required for analysis.

   keep if condition

5. -keep- only the record id.

   keep recno

6. Do a 1:1 merge back to the full dataset, with the -keep- and -keepusing- options to subset by record and variable at the same time.

   sort recno
   merge 1:1 recno using filename,nogenerate keep(master match) sorted keepusing(varlist2...)

7. Save the analysis dataset.

The full dataset needs to be in recno order for the -merge- to work. See the benchmarking script below for a complete example. Note that if we use 10% of the variables in the sample selection, and select 5% of the records, then the first step uses memory of only .5% of the original file unless there are cross-observation restrictions. If we then need 10% of the variables for the analysis, that step also uses the same amount of memory.

We have done some initial experiments, and found that with 100 variables in the full dataset, 10 variables in sample selection, and 10 different variables in analysis subsample our Linux PC can convert the full file to the analysis file at a rate of about 1 million full sample records per second. Doubling the number of variables used in both steps adds about 10% to that time. Nearly all the time is used in the merge - which appears to be inefficient compared to the other Stata steps. There is no noticable improvement with 4-core statamp. Increasing segmentsize to 1g helps a little.

Merging files

If datafiles are provided in sort order by record id on the disk, it should not be necessary to sort them before merging if the -sorted- option is specified on the merge statement. Actually, the master (in-core) dataset will need to be sorted before the merge, but this is very fast for an already sorted dataset.

Limitations

Nick Cox on sample selection

Here is the benchmark test program for the subsampling merge process.