Reshape

[Seyed Mahmoud Hosseinniakani]

Hi,
I am trying to reshape my data from wide to long, below is the example of my data. when I run the following code I receive r(9);.

reshape long AskPrice_ BidPrice_ , i(ISIN) j(day)

I checked and there is no duplicate observation in my data. I would be greatful if someone could help what does this error mean and how can I reshape my data properly.

Kind regards,
Mahmoud

Example
input str12 ISIN double(AskPrice_1 AskPrice_2 AskPrice_3 AskPrice_4 AskPrice_5 BidPrice_1 BidPrice_2 BidPrice_3 BidPrice_4 BidPrice_5)
"AT00000AMAG3" 28.495 29.98 29.435 30.48 29.995 28.04 28.905 29.155 29.16 29.845
"AT00000VIE62" 24.625 24.975 24.8675 24.7325 24.7825 24.565 24.9025 24.82 24.7125 24.7125
"AT0000644505" 63.786616 68.92301 70.430853 . . 63.689336 68.193409 70.343301 . .
"AT0000652250" 8.49 8.6 8.6 8.54 8.52 8.48 8.551 8.556 8.518 8.487
"AT0000676903" . . . . . . . . . .
"AT0000697750" . . . . . . . . . .
"AT0000720008" 4.984046 4.984046 5.034133 5.13525 5.198566 4.971761 4.962311 5.012397 5.126745 5.187226
"AT0000723606" 274.5 274 274 275 328 242 200 241 241 299
"AT0000743059" 25.39 24.83 24.73 24.405 23.99 25.355 24.77 24.7 24.4 23.95
"AT0000746409" 10.855 10.53 10.45 10.71 11.065 10.83 10.48 10.39 10.67 10.975
"AT0000758305" 25 24.76 23.69 24.4 23.8 24.82 24.66 23.52 24.215 23.725
"AT0000785555" 31.77 32 32 32 31.785 31.19 31.945 31.99 31.97 31.5
"AT0000800800" . . . . . . . . . .
"AT0000815402" 34.995 34.34 34.795 35.2 35.4 34.51 34.005 34.5 34.525 35.01
"AT0000820659" . . . . . . . . . .
"AT0000827209" .602746 .598925 .585552 .586508 .589373 .583642 .581731 .580776 .582687 .573134
"AT0000831706" 16.845 16.94 16.96 . . 16.79 16.925 16.925 . .
"AT0000938204" 104.5 104.4 103.5 102.95 103.35 102.65 103.95 102.4 102.3 103.25
"AT0000946652" 54.86 56.49 55.73 54.01 51.72 54.48 56.1 55.42 53.67 51.52
"AT0000A00XX9" 7.53 7.852 7.75 7.778 7.829 7.47 7.79 7.612 7.656 7.716
"AT0000A00Y78" 7.719 7.75 7.75 7.558 7.439 7.596 7.641 7.67 7.505 7.3
"AT0000A0E9W5" 6.468 6.55 6.63 6.67 6.688 6.394 6.54 6.501 6.641 6.606
"AT0000A18XM4" 26.6365643284835 22.4525932849522 21.8754316575564 22.9071019221981 22.9071435499322 26.5914179319926 22.407507169853 21.8303279975276 22.8619205852841 22.8620510700782
"AT0000APOST4" 32.702193 33.345639 34.121559 34.102634 34.580487 32.503481 33.236821 34.078978 34.041128 34.490594
"BE0003470755" 88.36 87.44 86.64 84.24 82.89 88.15 87.4 86.58 84.19 82.8
"BE0003555639" 30.89 30.01 30.35 30.255 30.38 30.86 29.935 30.325 30.21 30.315
"BE0003573814" 109 108.2 108.4 108 108.75 107.5 107.5 107.5 106.4 108
"BE0003654655" 29.88 29.88 29.88 29.88 29.88 10.02 10.02 10.02 10.02 10.02
"BE0003656676" 5.7 5.66 5.696 5.569 5.398 5.681 5.65 5.68 5.521 5.33
"BE0003700144" 1.521441 1.570519 1.570519 1.55416 1.560703 1.505081 1.538127 1.531256 1.529293 1.505408
"BE0003707214" 121.479334 120.758388 120.758388 117.15366 117.559192 119.451674 119.67697 119.001083 113.684109 116.252478
"BE0003731453" 101.8 100.5 100.5 100.5 100.5 101 99.51 98 95 100
"BE0003735496" 19.85 19.805 19.595 19.74 19.72 19.825 19.74 19.58 19.68 19.67
"BE0003739530" 71.32 68.48 69.3 70.12 71.29 71.02 68.39 69.16 69.98 71.2
"BE0003741551" 18.774216 18.654825 18.651094 18.408581 18.445891 18.580206 18.397388 18.3265 18.330231 18.356348
"BE0003743573" 18.2 18.19 18.2 18.2 18.495 18.1 18.14 17.85 17.8 17.995
"BE0003755692" 3.906 3.813 3.787 3.738 3.668 3.894 3.802 3.772 3.72 3.656
"BE0003760742" 100 99.5 100.2 101.5 101.5 98.9 99.05 99.05 100 100
"BE0003763779" 84.24 84.3 83.72 83.22 . 84.2 84.09 83.5 83.15 .
"BE0003766806" 36.3 36.265 35.95 36.38 35.87 36.15 36.035 35.88 36.155 35.745
"BE0003789063" 2.338 2.35 2.35 2.33 2.32 2.32 2.32 2.33 2.311 2.313
"BE0003790079" 63.5 63.03 62.93 63.15 63.35 63.26 62.89 62.67 62.94 62.97
"BE0003808251" 7.549 7.4 7.165 7.074 7.22 7.5 7.25 7.01 7.011 7.01
"BE0003810273" 29.9 29.945 29.61 29.9 29.79 29.86 29.915 29.575 29.885 29.765
"BE0003820371" 30.675 30.64 31.51 30.525 30.615 30.52 30.565 31.36 30.5 30.55
"BE0003825420" 10.77 10.8 12.14 12.14 12.14 10.5 10.8 10.8 10.8 10.8
"BE0003826436" 45.35 45.46 44.49 44.775 . 45.24 45.255 44.335 44.75 .
"BE0003839561" 59.38 60.33 61.28 . . 58.96 60 61 . .
"BE0003843605" 11.5 11.5 11.5 11.5 11.5 9.25 9.25 9.25 9.25 9.25
"BE0003844611" 3.69 3.58 3.6 3.6 3.54 3.581 3.569 3.513 3.59 3.501
"BE0003846632" 3.344 3.3 3.25 3.25 3.26 3.31 3.26 3.24 3.22 3.22
"BE0003858751" 26 26.5 26.45 26.45 26.58 25.5 25.65 25.5 26 26
"BE0003864817" . . . . . . . . . .
"BE0003877942" . . . . . . . . . .
"BE0003880979" 1.9 1.89 1.889 1.88

[Jorrit Gosens]

What is reported when you type:

isid ISIN
duplicates list ISIN

[Seyed Mahmoud Hosseinniakani]

Hi Jorrit,
Thanks for the help.

Below is what I get when I run "duplicates drop ISIN AskPrice_1, force" to drop duplicate, and I found no duplicate. But, when I ran the code you sent me, I see that there some duplicate in ISIN. Thus, I should drop the duplicate ISIN then.

. duplicates drop ISIN AskPrice_1, force

Duplicates in terms of ISIN AskPrice_1

(0 observations are duplicates)


Duplicates in terms of ISIN

+------------------------------+
| group: obs: ISIN |
|------------------------------|
| 1 323 DK0060696300 |
| 1 324 DK0060696300 |
| 2 1348 GB00B1GVQH21 |
| 2 1349 GB00B1GVQH21 |
| 3 835 GB00BYM8GJ06 |
|------------------------------|
| 3 836 GB00BYM8GJ06 |
| 4 842 GB00BYV81293 |
| 4 843 GB00BYV81293 |
+------------------------------+

[Seyed Mahmoud Hosseinniakani]

Should I drop the duplicated ISIN one by one or run a code to drop them automatically?

[Jorrit Gosens]

Correct. If you want to reshape long with ISIN as the sole ID variable, your dataset would need to contain no duplicates in that variable.
You have now identified that there are duplicates of ISIN, but with different values for other variables (at least in terms of AskPrice_1). You should probably have a look at the duplicates and decide whether dropping them straight away is the right strategy or not

Edit:
If you are sure you just wnat to get rid of all duplicates in temrs of ISIN, you can do:

duplicates drop ISIN, force

This will throw away all observations other than the first appearance of each ISIN. That means first observation base don current sort order.

[Seyed Mahmoud Hosseinniakani]

I decide to drop all duplicate, thus I use
duplicates drop ISIN, force Thanks for worthwhile help Jorrit!