* This model dBASE program assembles a nationwide database from state files.
* The current example copies selected fields from STF 1A for all places with
* 2500 or more inhabitants, and prompts the user to insert multiple CD-ROMs
* in succession.  The code can be modified to do analogous operations from
* other state-specific files from the STFs, CBP, agriculture census, etc.

fieldlist="statefp,cnty,cousubfp,placefp,anpsadpi,pop100,p0060001,p0060002"
cddrive="d:"
cumfile="c:\dbase\cumplace.dbf"
set safety off

* The state.dbf supplies the 2-character state abbreviation that is part of
* each file name; thus it drives the selection of state files in succession.
SELECT A
use c:\extract\1990aux\state index c:\extract\1990aux\statefp
set filter to sumlev="040"

done=.f.
do while .not. done
 do while .not. eof()
  stn=stusab
  *stusab (and now stn) is the 2-character state abbreviation
  if file(cddrive+"STF1A0"+stn+".dbf")
    *This set of operations will be done for every state found on this CD
    SELECT B
    use &cddrive.STF1A0&stn.
    *&cddrive. and &stn. are macro substitution that put the values of
    *cddrive and stn right into the command line.  The program executes as if
    *the line were written 'use d:STF1A0AL' when stn is AL.
    set index to &cddrive.STF1AM&stn.
    *The stf1am.. index groups all place total records together (sumlev 160)
    find "160"
    copy to temp fields &fieldlist for pop100>=2500 while sumlev="160"
    *This is where the real work is done, with the selected fields copied to
    *temp.dbf for the selected records
    if .not. file(cumfile)
      copy next 1 fields &fieldlist to &cumfile
    endif
    use &cumfile
    if reccount()=1
      zap
    endif
    append from temp
    *Here the latest state's data is added to the cumulative file

  endif
  SELECT A
  skip
  *"skip" makes the state data base go on to the record for the next state
 enddo

 *If all desired states are not on the same disk, you can repeat the process
 accept "Insert another disk and press enter, or type D if done: " to adone
 if upper(adone)="D"
   done=.t.
 endif
 go top
enddo

*Note that if the input came from multiple CDs, the states in the output file
*cumfile will not be in state code sequence, and you may want to index it.

*Paul Zeisset, Feb 25, 1994