Help on collapse

[Tehreem Husain]

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.

[Marcos Almeida]

You used the "if" clause to select code equals to 1, and this means both countries.