I have some data, a sample of which is attached below.
case
1 0 750 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 1500 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 0 0 4072
5 0 0 0 0 0 0 0 0 0 0 0 0 2745
5 0 0 0 0 0 0 0 0 0 512 0 0 0
5 0 0 0 0 0 0 0 0 0 0 0 0 1320
5 0 0 0 0 0 0 0 175 0 0 0 0 0
5 0 0 0 0 0 0 1760 0 0 0 0 0 0
5 0 6750 0 0 0 0 0 0 0 0 0 0 0
6 0 0 0 0 0 0 3760 0 0 0 0 0 0
6 0 4500 0 0 0 0 0 0 0 0 0 0 0
What I would like to do is to merge the data such that where there are multiple rows for one case, the data for each column variable is added together so there is a single row for each case.
I would also like the data to be in numeric order by case and to include the numbers not present such as case 4 with a row of all zeroes. This is in order to merge it with data I have which has cases from 1-5263 with one row for each case representing households. The data above represents people within the households so effectively what I would like to do is aggregate the data to the household level.
Any help would be much appreciated.
Thanks
Bookmarks