---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      name:  <unnamed>
       log:  /private[example working directory]/results/stata/analysis.log
  log type:  text
 opened on:  15 Oct 2026, 09:17:24

. tempfile master lookup

. import delimited "raw/firm-year.csv", clear varnames(1) stringcols(_all)
(encoding automatically selected: ISO-8859-1)
(5 vars, 16 obs)

. destring source_row, replace
source_row: all characters numeric; replaced as byte

. foreach v in firm_id year revenue employees {
  2.     replace `v' = strtrim(`v')
  3. }
(15 real changes made)
(15 real changes made)
(0 real changes made)
(0 real changes made)

. rename (year revenue employees) (year_raw revenue_raw employees_raw)

. generate str20 row_status = "kept"

. replace row_status = "invalid_key" if !ustrregexm(firm_id,"^[0-9]{5}$") | !ustrregexm(year_raw,"^[0-9]{4}$")
(2 real changes made)

. replace row_status = "invalid_value" if row_status=="kept" & ((!missing(revenue_raw) & !ustrregexm(revenue_raw,"^([0-9]+|[0-9]{1,3}(,[0-9]{3})+)([.][0-9]+)?$")) | (!missing(employees_raw) & !ustrregexm(employees_raw,"^[0-9]+$")))
(1 real change made)

. * 无效值已标记；强制转换只为分组计算，不能把转换产生的缺失当作有效输入。
. destring year_raw, generate(year) force
year_raw: contains nonnumeric characters; year generated as int
(1 missing value generated)

. destring revenue_raw, generate(revenue) ignore(",") force
revenue_raw: contains nonnumeric characters not specified in ignore()
revenue_raw: character , removed; revenue generated as int
(2 missing values generated)

. destring employees_raw, generate(employees) force
employees_raw: all characters numeric; employees generated as byte
(1 missing value generated)

. bysort firm_id year revenue employees (source_row): generate exact_rank = sum(row_status=="kept")

. replace row_status = "duplicate_removed" if row_status=="kept" & exact_rank>1
(1 real change made)

. bysort firm_id year: egen key_count = total(row_status=="kept")

. replace row_status = "key_conflict" if row_status=="kept" & key_count>1
(2 real changes made)

. quietly count

. local master_raw=r(N)

. quietly count if substr(row_status,1,7)=="invalid"

. local master_invalid=r(N)

. quietly count if row_status=="duplicate_removed"

. local master_duplicate_removed=r(N)

. quietly count if row_status=="key_conflict"

. local master_conflict_rows=r(N)

. preserve

. sort source_row

. export delimited source_row firm_id year_raw revenue_raw employees_raw row_status using "`outdir'/master-audit.csv", replace
(file results/stata/master-audit.csv not found)
file results/stata/master-audit.csv saved

. restore

. keep if row_status=="kept"
(6 observations deleted)

. keep source_row firm_id year revenue employees

. isid firm_id year

. sort firm_id year

. local master_clean=_N

. export delimited using "`outdir'/clean-master.csv", replace
(file results/stata/clean-master.csv not found)
file results/stata/clean-master.csv saved

. save `master'
file [Stata temporary file] saved as .dta format

. import delimited "raw/firm-region.csv", clear varnames(1) stringcols(_all)
(encoding automatically selected: ISO-8859-1)
(3 vars, 10 obs)

. destring lookup_row, replace
lookup_row: all characters numeric; replaced as byte

. replace firm_id=strtrim(firm_id)
(0 real changes made)

. replace region=strtrim(region)
(0 real changes made)

. generate str20 row_status="kept"

. replace row_status="invalid_key" if !ustrregexm(firm_id,"^[0-9]{5}$")
(1 real change made)

. replace row_status="invalid_value" if row_status=="kept" & missing(region)
(0 real changes made)

. bysort firm_id region (lookup_row): generate exact_rank=sum(row_status=="kept")

. replace row_status="duplicate_removed" if row_status=="kept" & exact_rank>1
(1 real change made)

. bysort firm_id: egen key_count=total(row_status=="kept")

. replace row_status="key_conflict" if row_status=="kept" & key_count>1
(2 real changes made)

. quietly count

. local lookup_raw=r(N)

. quietly count if substr(row_status,1,7)=="invalid"

. local lookup_invalid=r(N)

. quietly count if row_status=="duplicate_removed"

. local lookup_duplicate_removed=r(N)

. quietly count if row_status=="key_conflict"

. local lookup_conflict_rows=r(N)

. preserve

. sort lookup_row

. export delimited lookup_row firm_id region row_status using "`outdir'/lookup-audit.csv", replace
(file results/stata/lookup-audit.csv not found)
file results/stata/lookup-audit.csv saved

. restore

. keep if row_status=="kept"
(4 observations deleted)

. keep lookup_row firm_id region

. isid firm_id

. sort firm_id

. local lookup_clean=_N

. export delimited using "`outdir'/clean-lookup.csv", replace
(file results/stata/clean-lookup.csv not found)
file results/stata/clean-lookup.csv saved

. save `lookup'
file [Stata temporary file] saved as .dta format

. use `master', clear

. merge m:1 firm_id using `lookup'

    Result                      Number of obs
    -----------------------------------------
    Not matched                             3
        from master                         2  (_merge==1)
        from using                          1  (_merge==2)

    Matched                                 8  (_merge==3)
    -----------------------------------------

. generate str12 merge_status=cond(_merge==3,"matched",cond(_merge==1,"master_only","lookup_only"))

. quietly count if _merge==3

. local matched=r(N)

. quietly count if _merge==1

. local master_only=r(N)

. quietly count if _merge==2

. local lookup_only=r(N)

. local merged_all=_N

. assert `matched'+`master_only'==`master_clean'

. order source_row firm_id year revenue employees lookup_row region merge_status

. sort firm_id year

. export delimited source_row firm_id year revenue employees lookup_row region merge_status using "`outdir'/merged-all.csv", replace
(file results/stata/merged-all.csv not found)
file results/stata/merged-all.csv saved

. keep if _merge!=2
(1 observation deleted)

. local analysis_ready=_N

. quietly count if missing(revenue)

. local missing_revenue=r(N)

. quietly count if missing(employees)

. local missing_employees=r(N)

. export delimited source_row firm_id year revenue employees lookup_row region merge_status using "`outdir'/analysis-ready.csv", replace
(file results/stata/analysis-ready.csv not found)
file results/stata/analysis-ready.csv saved

. file open output using "`outdir'/counts.csv", write replace
(file results/stata/counts.csv not found)

. file write output "metric,value" _n

. foreach metric in master_raw master_invalid master_duplicate_removed master_conflict_rows master_clean lookup_raw lookup_invalid lookup_duplicate_removed lookup_conflict_rows lookup_clean matched master_only lookup_only merged_all analysis_ready missing
> _revenue missing_employees {
  2.     file write output "`metric',``metric''" _n
  3. }

. file close output

. display "CLEAN_MERGE_STATA_COMPLETED"
CLEAN_MERGE_STATA_COMPLETED

. log close
      name:  <unnamed>
       log:  /private[example working directory]/results/stata/analysis.log
  log type:  text
 closed on:  15 Oct 2026, 09:17:24
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------