Complex Reshape Long to Wide

[Olena Onishchenko]

Dear Stata Users

I am dealing with a complex reshape wide problem.

Here is an input data:

* 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 tradevolume str4 tradingreport str1(ordertype invtype) str4 ordernumber1
19725 "1512  " "B" "0" 32400000      1 "m5584"   8.8  2000 "0057" "0" "I" "476R"
19725 "1512  " "S" "0" 32400000      1 "N5683"   8.8  2000 "0307" "3" "I" "3554"
19725 "1512  " "B" "0" 32400000      2 "C5523"   8.8  8000 "1926" "0" "I" "7631"
19725 "1512  " "S" "0" 32400000      2 "N5683"   8.8  8000 "0307" "3" "I" "3554"
19725 "1512  " "B" "0" 32400000      3 "C5523"   8.8  2000 "1926" "0" "I" "7631"
19725 "1512  " "S" "0" 32400000      3 "45557"   8.8  2000 "1131" "0" "J" "7966"
19725 "0050  " "B" "0" 32400000      4 "U5556"  58.7  2000 "0298" "3" "I" "722E"
19725 "0050  " "S" "0" 32400000      4 "N5883"  58.7  2000 "0614" "4" "I" "882H"
19725 "2311  " "B" "0" 32404000   2414 "T5582"    28  1000 "1031" "3" "I" "7036"
19725 "2311  " "S" "0" 32404000   2414 "C5501"    28  5000 "0437" "3" "I" "4909"
19725 "2311  " "B" "0" 32404000   2414 "T5582"    28  4000 "1031" "0" "I" "7036"
19725 "2330  " "B" "0" 32405000   3004 "o5551"   105  3000 "2612" "0" "I" "4768"
19725 "2330  " "B" "0" 32405000   3004 "o5551"   105  2000 "2612" "4" "I" "4768"
19725 "2330  " "S" "0" 32405000   3004 "N5819"   105  5000 "0054" "6" "F" "8465"
19725 "2406  " "B" "0" 32405000   3197 "G5551"  34.3  8000 "1920" "0" "I" "3345"
19725 "2406  " "S" "0" 32405000   3197 "G5584"  34.3 25000 "1044" "3" "I" "490I"
19725 "2406  " "B" "0" 32405000   3197 "G5551"  34.3 17000 "1920" "1" "I" "3345"
19725 "0050  " "S" "2" 52200000 884583 "28269" 58.55    70 "1135" "0" "I" "001A"
19725 "0050  " "B" "2" 52200000 884583 "F5354" 58.55    70 "0920" "0" "I" "708R"
19725 "0050  " "B" "2" 52200000 884584 "F5354" 58.55    30 "0920" "0" "I" "708R"
19725 "0050  " "S" "2" 52200000 884584 "12556" 58.55    30 "0252" "0" "I" "4906"
end
format %tdDD/NN/CCYY date
format %tc_HH:MM:SS tradetime

I have attached an output file I would like to produce in stata.

I have tried the following code which did not work:

reshape wide tradevolume tradeprice, i(tradenumber) j(invtype ordertype buy_sell) string stata forum example.xlsx

Any idea of how to perform the desired reshape?

Thank you.

[Justin Niakamal]

The issue here is tradenumber is not unique. Per the manual, " i(varlist) specifies the variables whose unique values denote a logical observation."

As an example

gen id = _n 
reshape wide tradevolume tradeprice, i(tradenumber id ) j(invtype ordertype buy_sell) string

will work because i() is made of unique values. I don't endorse this, as it's likely not appropriate, but just for illustrative purposes.

[Olena Onishchenko]

Justin

I have tested your code suggestion. It does not generate the desired output as shown in excel table above. The code just drops (not reshapes) 3 variables: invtype, tradevolume, tradeprice.

I am aware that tradenumber is not unique....it's the same for buy_sell (B, S) and in some cases it's repeated within buys or sells as one buy trade is executed against two sells.

Any other working suggestions around it?

Thank you.