Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Loop, preserve, save, merge, restore

    Dear Stata Users

    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
    I am hopeful that experienced STATA users can help me to write a loop to do it intelligently over many files.

    Thanks a lot in advance.
    Tags: None
  • #2
    It appears that buy_sell is either "B" or "S", ordertype = "0", "1", ..., "6", and invtype = "I", "M", "F", or "J". If that is true, the following untested code might point you in a useful direction.
    Code:
    foreach v1 in B S {
foreach v2 in 0 1 2 3 4 5 6 {
foreach v3 in I M F J {
    generate TV_`v1'`v2'`v3' = tradevolume if  buy_sell=="v1" & ordertype=="v2" & invtype=="v3"
}
}
}
    With that said, I also expect the following untested code would work.
    Code:
    generate c = buy_sell + ordertype + invtype
rename tradevolume TV_
reshape wide TV_, i(date tradenumber) j(c) string
    Last edited by William Lisowski; 08 Nov 2019, 19:57.

    Comment

    • #3
      William

      Thank you.

      I just tired reshape and got an error:

      Code:
      generate c= buy_sell+ ordertype+ invtype
rename tradevolume TV_
reshape wide TV_, i (date tradenumber) j(c) string

variable buy_sell not constant within tradenumber
variable ordernumber not constant within tradenumber
variable tradingreport not constant within tradenumber
variable ordertype not constant within tradenumber
variable invtype not constant within tradenumber
variable ordernumber1 not constant within tradenumber
      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input float date str6 code str1(buy_sell tradercode) double tradetime long tradenumber str5 ordernumber double tradeprice long TV_ str4 tradingreport str1(ordertype invtype) str4 ordernumber1 str3 c
19725 "1512  " "B" "0" 32400000      1 "m5584"   8.8  2000 "0057" "0" "I" "476R" "B0I"
19725 "1512  " "S" "0" 32400000      1 "N5683"   8.8  2000 "0307" "3" "I" "3554" "S3I"
19725 "1512  " "B" "0" 32400000      2 "C5523"   8.8  8000 "1926" "0" "I" "7631" "B0I"
19725 "1512  " "S" "0" 32400000      2 "N5683"   8.8  8000 "0307" "3" "I" "3554" "S3I"
19725 "1512  " "B" "0" 32400000      3 "C5523"   8.8  2000 "1926" "0" "I" "7631" "B0I"
19725 "1512  " "S" "0" 32400000      3 "45557"   8.8  2000 "1131" "0" "J" "7966" "S0J"
19725 "0050  " "B" "0" 32400000      4 "U5556"  58.7  2000 "0298" "3" "I" "722E" "B3I"
19725 "0050  " "S" "0" 32400000      4 "N5883"  58.7  2000 "0614" "4" "I" "882H" "S4I"
19725 "2311  " "B" "0" 32404000   2414 "T5582"    28  1000 "1031" "3" "I" "7036" "B3I"
19725 "2311  " "S" "0" 32404000   2414 "C5501"    28  5000 "0437" "3" "I" "4909" "S3I"
19725 "2311  " "B" "0" 32404000   2414 "T5582"    28  4000 "1031" "0" "I" "7036" "B0I"
19725 "2330  " "B" "0" 32405000   3004 "o5551"   105  3000 "2612" "0" "I" "4768" "B0I"
19725 "2330  " "B" "0" 32405000   3004 "o5551"   105  2000 "2612" "4" "I" "4768" "B4I"
19725 "2330  " "S" "0" 32405000   3004 "N5819"   105  5000 "0054" "6" "F" "8465" "S6F"
19725 "2406  " "B" "0" 32405000   3197 "G5551"  34.3  8000 "1920" "0" "I" "3345" "B0I"
19725 "2406  " "S" "0" 32405000   3197 "G5584"  34.3 25000 "1044" "3" "I" "490I" "S3I"
19725 "2406  " "B" "0" 32405000   3197 "G5551"  34.3 17000 "1920" "1" "I" "3345" "B1I"
19725 "0050  " "S" "2" 52200000 884583 "28269" 58.55    70 "1135" "0" "I" "001A" "S0I"
19725 "0050  " "B" "2" 52200000 884583 "F5354" 58.55    70 "0920" "0" "I" "708R" "B0I"
19725 "0050  " "B" "2" 52200000 884584 "F5354" 58.55    30 "0920" "0" "I" "708R" "B0I"
19725 "0050  " "S" "2" 52200000 884584 "12556" 58.55    30 "0252" "0" "I" "4906" "S0I"
end
format %tdDD/NN/CCYY date
format %tc_HH:MM:SS tradetime

      The problem here is that tradenumber is not unique as it is repeated for buy_sell, ordertype, invtype...

      Is there any way to reshape it avoiding loops?

      Regards,
      Olena

      Comment

      • #4
        Try dropping those six variables after the generate command.

        Either that, or just drop the first three, and add the last three to the reshape command.

        Comment

        • #5
          William

          Thank you. Your suggestion works on the individual file. But when I add reshape command to a loop it returns an error:

          Code:
          clear
cd "\\registry\obs\obsUser\oniol94p\Data\Documents\m\TWSE"
clear
set more off
local myfilelist : dir . files "*.csv"
foreach file of local myfilelist {
insheet using `file', clear
gen date1=substr(v1, 1,8)
generate date=date( date,"YMD")
format %tdDD/NN/CCYY date
drop date1
gen code=substr(v1, 9,6)
gen buy_sell=substr(v1,15,1)
gen tradercode=substr(v1,16,1)
gen tradetime1=substr(v1,17,8)
generate double tradetime = clock( tradetime1 ,"hms#")
format tradetime %tc_HH:MM:SS
drop tradetime1
gen tradenumber=substr(v1,25,8)
destring tradenumber , replace
gen tradeprice=substr(v1,38,7)
destring tradeprice, replace
gen tradevolume=substr(v1,45,9)
destring tradevolume, replace
gen ordertype=substr(v1,58,1)
gen invtype=substr(v1,59,1)
drop v1
generate c = buy_sell + ordertype + invtype
rename tradevolume TV_
drop buy_sell tradercode ordertype invtype
reshape wide TV_, i(date tradenumber) j(c) string
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}
          tradenumber: all characters numeric; replaced as long
          tradeprice: all characters numeric; replaced as double
          tradevolume: all characters numeric; replaced as long
          (note: j = B0F B0I B0J B0M B1I B1J B2I B3I B3J B4I B4J S0F S0I S0J S0M S1I S1J S2I S2J S3I S3J S4I S4J S5F S6F S6J)
          values of variable c not unique within date tradenumber
          Your data are currently long. You are performing a reshape wide. You specified i(date tradenumber) and j(c). There are observations within i(date
          tradenumber) with the same value of j(c). In the long data, variables i() and j() together must uniquely identify the observations.

          Comment

          • #6
            It appears that one of your input files has a problem in its data.

            You need to use the duplicates commands to find the duplicates in that file. Then you need to determine how to correct your data.

            Read the output of help duplicates for instructions on using it. The following commands may be helpful.
            Code:
            sort date tradenumber c
duplicates report date tradenumber c 
duplicates tag date tradenumber c, generate(copies)
list if copies>0

            Comment

            • #7
              William

              Thank you.You are right, there are duplicates.

              I need to run a loop via files, detect duplicates and rename files with duplicates. If there are no duplicates leave file names as it is.

              Code:
              clear
cd "D:\WORKING\TWSE"
local allfiles : dir . files "*.dta"
foreach f in `allfiles' {
use `f', clear
generate c = buy_sell + ordertype + invtype
rename tradevolume TV_
sort date tradenumber c
duplicates tag date tradenumber c, generate(copies)
save files under different name if copies!=0
}
              How do I alter the code so it changes file's name to the files with duplicates only (if copies!=0)?

              Regards,
              Olena

              Comment

              • #8
                One way around is to add "capture noisily" to reshape command. Then it ignores files with duplicates.

                Code:
                clear
cd "\\registry\obs\obsUser\oniol94p\Data\Documents\m\TWSE\2015\m01"
local allfiles : dir . files "*.dta"
foreach f in `allfiles' {
use `f', clear
generate c = buy_sell + ordertype + invtype
rename tradevolume TV_
drop buy_sell tradercode ordertype invtype
capture noisily reshape wide TV_, i(date tradenumber) j(c) string
save `f', replace
}
                But I need some sort of a flag for files with duplicates so I can go via these files manually.

                Thanks.

                Comment

                • #9
                  What you have written using the if clause will save observations that are not duplicates.

                  In post #7 you want something like the following.

                  Code:
                  clear
cd "D:\WORKING\TWSE"
local allfiles : dir . files "*.dta"
foreach f in `allfiles' {
use `f', clear
generate c = buy_sell + ordertype + invtype
rename tradevolume TV_
sort date tradenumber c
duplicates tag date tradenumber c, generate(copies)
summarize copies
if r(N)>0 {
    save file under different name
}
}

                  Comment

                  Previous Next
                  Working...
                  X