cap:mkdir 0306a //krabs_0306a_85 use "D:\Stata17\0306a\CA_util_data_20_21_FINAL.dta",clearimport excel "D:\Stata17\0306a\数据\数据\hhah13utildatafinal.xlsx", sheet("Sec 1-4") firstrow clear drop in 1/3cd "D:\Stata17\0306a\数据\数据" fs hh*.xlsxforeach file in `r(files)'{ local excel_file "`file'" // local ci=0quietly import excel "`excel_file'",describelocal ci=0forvalues s=1/`r(N_worksheet)'{local cm `r(worksheet_`s')'local cmn=ustrregexra("`cm'","[^0-9a-zA-Z\u4e00-\u9fa5]+","") local cw="20"+ustrregexra("`file'","[^0-9]+","") local cmn1=lower("`cmn'")if regexm("`cmn1'","tips"){continue}quietly import excel "`excel_file'", sheet("`r(worksheet_`s')'") cellrange(`r(range_`s')') firstrow clearlocal cx `cmn'`s' // disp "`cx'"if _N>0{local ci=`ci'+1 ccap:rename facno fac_nocap:rename facility_no fac_nocap:rename facility_num fac_nocap:rename provider_no fac_nocap:rename facname fac_namecap:rename facility_name fac_namecap:rename provider_name fac_namecap:rename address fac_str_addrcap:rename fac_address fac_str_addrcap:rename facility_address fac_str_addrcap:rename city fac_citycap:rename facility_city fac_citycap:rename zip fac_zipcap:rename zipcode fac_zipcap:rename facility_zip fac_zipcap:rename phone fac_phonecap:rename phone_no fac_phonecap:rename facility_phone fac_phonecap:rename admin fac_admin_namecap:rename administrator fac_admin_namecap:rename administrator_name fac_admin_namecap:rename type_cntrl hhah_hospice_licee_toccap:rename type_control hhah_hospice_licee_toccap:rename type_cntrl_choice hhah_hospice_licee_tocsave a`cw'_`ci',replacedi "`file'"_"工作表 [`cm'] 已保存为：a`ci'.dta"}}} use a2023_1,clearfs a*.dta foreach file in `r(files)'{use "`file'",clearcap:rename OSHPD_ID FAC_NOreplace FAC_NO=subinstr(FAC_NO," ","",.)replace FAC_NO=trim(FAC_NO)keep if strlen(FAC_NO)==9local c1=subinstr("`file'",".dta","",.)local c1=subinstr("`c1'","a","",.)tostring _all,replace forcesave b`c1',replace } forvalues ii=2013/2024{fs b`ii'*use b`ii'_1,clearforeach file in `r(files)'{if "`file'"=="a"+"`ii'"+"_1.dta"{continue}merge 1:1 FAC_NO using "`file'"drop _merge}save cc`ii',replace }fs *.xlsx foreach file in `r(files)'{disp "`file'"local c1="20"+ustrregexra("`file'","[^0-9]+","",.)local uu=0if "`c1'"=="2015"{import excel using "`file'", sheet("Sect 1-4") firstrow clearlocal uu=1}if "`c1'"=="2016" | "`c1'"=="2017"{import excel using "`file'", sheet("Section 1-4") firstrow clearlocal uu=1}import excel using "`file'", sheet("Sec 1-4") firstrow cleardrop in 1/3disp "`c1'"gen year="`c1'"save aa20`c1',replace } local c1=ustrregexra("`file'","[^0-9]+"," ",.)