I need to change the data layout for 1164 daily files. The names of the files corresponds to a specific calendar date (i.e.mth20140102 is January 2nd, 2014). I cannot append them to bigger files as they become too big to manage (my computer processing power cannot handle it).
Essentially I need to transpose the data step by step, keeping one characteristic fist (i.e buy+order type "0"+investor type "I"), adding sequentially the others and saving the file.
Reshape command does not work as there is no unique identifier in the input files and creating one does not help either.
I have written necessary STATA commands to transform one file:
Code:
****************************I******************************************* ************************************************************************ use "G:\TWSE\data\2014__Jan\mth20140102.dta" preserve keep if buy_sell=="B" & ordertype=="0" & invtype=="I" rename tradevolume TV_B0I save "G:\TWSE\data\2014_02_Jan\20140102.dta" restore preserve keep if buy_sell=="S" & ordertype=="0" & invtype=="I" rename tradevolume TV_S0I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="1" & invtype=="I" rename tradevolume TV_B1I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="1" & invtype=="I" rename tradevolume TV_S1I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="2" & invtype=="I" rename tradevolume TV_B2I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="2" & invtype=="I" rename tradevolume TV_S2I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="3" & invtype=="I" rename tradevolume TV_B3I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="3" & invtype=="I" rename tradevolume TV_S3I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="4" & invtype=="I" rename tradevolume TV_B4I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="4" & invtype=="I" rename tradevolume TV_S4I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="5" & invtype=="I" rename tradevolume TV_B5I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="5" & invtype=="I" rename tradevolume TV_S5I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="6" & invtype=="I" rename tradevolume TV_B6I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="6" & invtype=="I" rename tradevolume TV_S6I merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore clear all *************************************************************** *********************M**************************************** *************************************************************** use "G:\TWSE\data\2014__Jan\mth20140102.dta" preserve keep if buy_sell=="B" & ordertype=="0" & invtype=="M" rename tradevolume TV_B0M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="0" & invtype=="M" rename tradevolume TV_S0M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="1" & invtype=="M" rename tradevolume TV_B1M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="1" & invtype=="M" rename tradevolume TV_S1M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="2" & invtype=="M" rename tradevolume TV_B2M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="2" & invtype=="M" rename tradevolume TV_S2M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="3" & invtype=="M" rename tradevolume TV_B3M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="3" & invtype=="M" rename tradevolume TV_S3M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="4" & invtype=="M" rename tradevolume TV_B4M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="4" & invtype=="M" rename tradevolume TV_S4M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="5" & invtype=="M" rename tradevolume TV_B5M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="5" & invtype=="M" rename tradevolume TV_S5M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="6" & invtype=="M" rename tradevolume TV_B6M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="6" & invtype=="M" rename tradevolume TV_S6M merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore clear all **************************F*************************** use "G:\TWSE\data\2014__Jan\mth20140102.dta" preserve keep if buy_sell=="B" & ordertype=="0" & invtype=="F" rename tradevolume TV_B0F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="0" & invtype=="F" rename tradevolume TV_S0F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="1" & invtype=="F" rename tradevolume TV_B1F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="1" & invtype=="F" rename tradevolume TV_S1F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="2" & invtype=="F" rename tradevolume TV_B2F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="2" & invtype=="F" rename tradevolume TV_S2F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="3" & invtype=="F" rename tradevolume TV_B3F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="3" & invtype=="F" rename tradevolume TV_S3F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="4" & invtype=="F" rename tradevolume TV_B4F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="4" & invtype=="F" rename tradevolume TV_S4F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="5" & invtype=="F" rename tradevolume TV_B5F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="5" & invtype=="F" rename tradevolume TV_S5F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="6" & invtype=="F" rename tradevolume TV_B6F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="6" & invtype=="F" rename tradevolume TV_S6F merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore clear all ************************J****************************** use "G:\TWSE\data\2014__Jan\mth20140102.dta" preserve keep if buy_sell=="B" & ordertype=="0" & invtype=="J" rename tradevolume TV_B0J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="0" & invtype=="J" rename tradevolume TV_S0J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="1" & invtype=="J" rename tradevolume TV_B1J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="1" & invtype=="J" rename tradevolume TV_S1J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="2" & invtype=="J" rename tradevolume TV_B2J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="2" & invtype=="J" rename tradevolume TV_S2J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="3" & invtype=="J" rename tradevolume TV_B3J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="3" & invtype=="J" rename tradevolume TV_S3J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="4" & invtype=="J" rename tradevolume TV_B4J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="4" & invtype=="J" rename tradevolume TV_S4J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="5" & invtype=="J" rename tradevolume TV_B5J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="5" & invtype=="J" rename tradevolume TV_S5J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="B" & ordertype=="6" & invtype=="J" rename tradevolume TV_B6J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace restore preserve keep if buy_sell=="S" & ordertype=="6" & invtype=="J" rename tradevolume TV_S6J merge 1:1 date tradenumber using "G:\TWSE\data\2014_02_Jan\20140102.dta" drop _merge drop buy_sell ordertype invtype save "G:\TWSE\data\2014_02_Jan\20140102.dta", replace clear all
Thanks a lot in advance.
0 Response to Loop, preserve, save, merge, restore
Post a Comment