Hello! I am working on historical financial data. A small sample of my data looks like this:
autonumber year month country amount presentamount openprice hiprice loprice countrycode
67 1869 1 GERMANY 7421500 0 0 62 58 1
87 1869 1 BAVARIAN 700000 0 0 100 98 1
264 1870 1 SOUTH AFRICA 227000 0 0 0 0 22
264 1870 1 ORANGE STATE 225000 0 0 99 98 22
264 1870 1 TRANSVAAL 10000 0 0 102 101 22
233 1869 1 CANADIAN 0 0 0 107.5 103.87 23
183 1869 1 UNITED STATES 413300 0 0 91 89 10
19 1869 1 NEW GRANADA 3083500 0 0 7 6.25 32
I want to take a sum of the amount and present amount and an average of the open price, hiprice and loprice if the country codes are the same. There are regions with countries which I want clubbed. I have assigned them a similar code. Like Bavaria and Germany are given a similar code. Same goes for different regions which were part of present day South Africa. I tried the following command:

collapse (sum) amount (sum) presentamount (mean) openprice (mean) hiprice (mean) loprice if countrycode ==1, by (autonumber year month country)

This results in dropping all the rest except Germany and Bavarian. Where am I going wrong? Need your help.Thanks.