How to manipulate row values.

[chintu red]

I have age group wise data, however, in the extracted data we have age groups 75-79, 75+ and all ages. Now I want to replace the 75+ age group value as value of 75+ minus value of 75-79, so that I can get 80+ years values. The structure of the data is as follows. I have other age groups also (16 age groups in total).

for exmaple we have for a given 75-79 values as 106.98, and 75+239 now I want 239-106 as my new against 75+ .


* Example generated by -dataex-. For more info, type help dataex
clear
input int age_id str11 age_name int year float total
22 "All ages" 2010 16144.26
22 "All ages" 2010 20424.084
22 "All ages" 2010 36568.344
20 "75-79 years" 2010 106.98487
20 "75-79 years" 2010 130.61581
20 "75-79 years" 2010 237.6007
22 "All ages" 2010 10454.585
22 "All ages" 2010 8897.344
22 "All ages" 2010 19351.93
234 "75+ years" 2010 239.20107
234 "75+ years" 2010 214.5428
234 "75+ years" 2010 453.7438
20 "75-79 years" 2010 0
20 "75-79 years" 2010 0
20 "75-79 years" 2010 0
22 "All ages" 2010 32438.547
22 "All ages" 2010 33387.57
22 "All ages" 2010 65826.12
234 "75+ years" 2010 0
234 "75+ years" 2010 0
234 "75+ years" 2010 0
20 "75-79 years" 2010 116.1787
20 "75-79 years" 2010 134.4952
20 "75-79 years" 2010 250.6739
22 "All ages" 2010 1746.4004
22 "All ages" 2010 1761.079
22 "All ages" 2010 3507.479
234 "75+ years" 2010 448.2208
234 "75+ years" 2010 486.802
234 "75+ years" 2010 935.0228
20 "75-79 years" 2010 103.35564
20 "75-79 years" 2010 215.7519
20 "75-79 years" 2010 319.1075
22 "All ages" 2010 29641.96
22 "All ages" 2010 30594.39
22 "All ages" 2010 60236.35
234 "75+ years" 2010 123.16811
234 "75+ years" 2010 240.84067
234 "75+ years" 2010 364.0088
20 "75-79 years" 2010 0
20 "75-79 years" 2010 0
20 "75-79 years" 2010 0
22 "All ages" 2010 0
22 "All ages" 2010 0
22 "All ages" 2010 0
234 "75+ years" 2010 0
234 "75+ years" 2010 0
234 "75+ years" 2010 0
20 "75-79 years" 2010 25.038107
20 "75-79 years" 2010 14.910136
20 "75-79 years" 2010 39.94824
22 "All ages" 2010 3898.867
22 "All ages" 2010 1838.3585
22 "All ages" 2010 5737.226
20 "75-79 years" 2010 20062.254
20 "75-79 years" 2010 13640.828
20 "75-79 years" 2010 33703.082
22 "All ages" 2010 299455.63
22 "All ages" 2010 160457.6
22 "All ages" 2010 459913.2
234 "75+ years" 2010 38501.38
234 "75+ years" 2010 25151.5
234 "75+ years" 2010 63652.88
22 "All ages" 2010 1628.5184
22 "All ages" 2010 1628.5184
22 "All ages" 2010 1712.9108
22 "All ages" 2010 1712.9108
22 "All ages" 2010 6688.983
22 "All ages" 2010 6688.983
22 "All ages" 2010 2418.917
22 "All ages" 2010 2418.917
22 "All ages" 2010 9263.8955
22 "All ages" 2010 9263.8955
22 "All ages" 2010 138465.19
22 "All ages" 2010 113279.87
22 "All ages" 2010 251745.05
22 "All ages" 2010 73869.59
22 "All ages" 2010 54093.7
22 "All ages" 2010 127963.3
22 "All ages" 2010 23257.264
22 "All ages" 2010 23070.545
22 "All ages" 2010 46327.81
22 "All ages" 2010 17968.08
22 "All ages" 2010 16691.229
22 "All ages" 2010 34659.31
22 "All ages" 2010 100106.96
22 "All ages" 2010 86876.4
22 "All ages" 2010 186983.36
20 "75-79 years" 2010 305.7084
20 "75-79 years" 2010 282.0884
20 "75-79 years" 2010 587.79675
22 "All ages" 2010 13957.778
22 "All ages" 2010 22891.8
22 "All ages" 2010 36849.58
22 "All ages" 2010 34695.824
22 "All ages" 2010 30351.275
22 "All ages" 2010 65047.1
234 "75+ years" 2010 5826.542
234 "75+ years" 2010 5721.97
234 "75+ years" 2010 11548.512
end
[/CODE]


Thanks

[Daniel Schaefer]

I'm afraid I have no idea what you are asking for here.

for exmaple we have for a given 75-79 values as 106.98

so by "value" you mean the corresponding value of the total column?

I have other age groups also (16 age groups in total).

What is an "age group". Are age groups categories, like "75+ years" or "All ages"? Or are age groups denoted by the value of the age_id variable?

and 75+239 now I want 239-106 as my new against 75+ .

You've completely lost me. So you are looking at this row:

20 "75-79 years" 2010 106.98487

And this row:

234 "75+ years" 2010 239.20107

And you want to subtract the value in the total column for the first row from the second? To get what? A new variable? What exactly do these two rows have in common? How would I know, in general, which two rows should be subtracted to (I assume) make a new variable? Which of these two rows should the new value go in?

[Ken Chui]

Agree with #2, put that in another way, if I deleted all the cases for "All ages" for the sake of simplicity, I will get this data:

     +----------------------------------------+
     | age_id      age_name   year      total |
     |----------------------------------------|
  1. |     20   75-79 years   2010   215.7519 |
  2. |     20   75-79 years   2010          0 |
  3. |     20   75-79 years   2010   116.1787 |
  4. |     20   75-79 years   2010   33703.08 |
  5. |     20   75-79 years   2010   250.6739 |
  6. |     20   75-79 years   2010          0 |
  7. |     20   75-79 years   2010   587.7968 |
  8. |     20   75-79 years   2010   20062.25 |
  9. |     20   75-79 years   2010   134.4952 |
 10. |     20   75-79 years   2010   282.0884 |
 11. |     20   75-79 years   2010   25.03811 |
 12. |     20   75-79 years   2010          0 |
 13. |     20   75-79 years   2010   39.94824 |
 14. |     20   75-79 years   2010   106.9849 |
 15. |     20   75-79 years   2010          0 |
 16. |     20   75-79 years   2010   319.1075 |
 17. |     20   75-79 years   2010   237.6007 |
 18. |     20   75-79 years   2010   130.6158 |
 19. |     20   75-79 years   2010   13640.83 |
 20. |     20   75-79 years   2010   305.7084 |
 21. |     20   75-79 years   2010   103.3556 |
 22. |     20   75-79 years   2010          0 |
 23. |     20   75-79 years   2010   14.91014 |
 24. |     20   75-79 years   2010          0 |
     |----------------------------------------|
 25. |    234     75+ years   2010   239.2011 |
 26. |    234     75+ years   2010   240.8407 |
 27. |    234     75+ years   2010   453.7438 |
 28. |    234     75+ years   2010   11548.51 |
 29. |    234     75+ years   2010   448.2208 |
 30. |    234     75+ years   2010   123.1681 |
 31. |    234     75+ years   2010    486.802 |
 32. |    234     75+ years   2010    25151.5 |
 33. |    234     75+ years   2010   5826.542 |
 34. |    234     75+ years   2010   38501.38 |
 35. |    234     75+ years   2010          0 |
 36. |    234     75+ years   2010          0 |
 37. |    234     75+ years   2010          0 |
 38. |    234     75+ years   2010    5721.97 |
 39. |    234     75+ years   2010   214.5428 |
 40. |    234     75+ years   2010          0 |
 41. |    234     75+ years   2010          0 |
 42. |    234     75+ years   2010   63652.88 |
 43. |    234     75+ years   2010   364.0088 |
 44. |    234     75+ years   2010   935.0228 |
 45. |    234     75+ years   2010          0 |
     +----------------------------------------+

There are 24 cases of "75-79 years" and 21 cases of "75+ years", but there are no other variables telling which pair of each should go into the subtraction. It seems some other crucial variables were omitted in the data example.

[chintu red]

Dear Sir
Sorry for my vague question.

The data set contains sex_id (female, male and both) age_id age_name cause_id (for a disease) and then total number of deaths from a disease.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(measure_id sex_id) int age_id str11 age_name int(cause_id year) float total
1 1 22 "All ages" 339 2010 16144.26
1 2 22 "All ages" 339 2010 20424.084
1 3 22 "All ages" 339 2010 36568.344
1 1 20 "75-79 years" 340 2010 106.98487
1 2 20 "75-79 years" 340 2010 130.61581
1 3 20 "75-79 years" 340 2010 237.6007
1 1 22 "All ages" 340 2010 10454.585
1 2 22 "All ages" 340 2010 8897.344
1 3 22 "All ages" 340 2010 19351.93
1 1 234 "75+ years" 340 2010 239.20107
1 2 234 "75+ years" 340 2010 214.5428
1 3 234 "75+ years" 340 2010 453.7438
1 1 20 "75-79 years" 341 2010 0
1 2 20 "75-79 years" 341 2010 0
1 3 20 "75-79 years" 341 2010 0
1 1 22 "All ages" 341 2010 32438.547
1 2 22 "All ages" 341 2010 33387.57
1 3 22 "All ages" 341 2010 65826.12
1 1 234 "75+ years" 341 2010 0
1 2 234 "75+ years" 341 2010 0
1 3 234 "75+ years" 341 2010 0
1 1 20 "75-79 years" 342 2010 116.1787
1 2 20 "75-79 years" 342 2010 134.4952
1 3 20 "75-79 years" 342 2010 250.6739
1 1 22 "All ages" 342 2010 1746.4004
1 2 22 "All ages" 342 2010 1761.079
1 3 22 "All ages" 342 2010 3507.479
1 1 234 "75+ years" 342 2010 448.2208
1 2 234 "75+ years" 342 2010 486.802
1 3 234 "75+ years" 342 2010 935.0228
1 1 20 "75-79 years" 345 2010 103.35564
1 2 20 "75-79 years" 345 2010 215.7519
1 3 20 "75-79 years" 345 2010 319.1075
1 1 22 "All ages" 345 2010 29641.96
1 2 22 "All ages" 345 2010 30594.39
1 3 22 "All ages" 345 2010 60236.35
1 1 234 "75+ years" 345 2010 123.16811
1 2 234 "75+ years" 345 2010 240.84067
1 3 234 "75+ years" 345 2010 364.0088
1 1 20 "75-79 years" 346 2010 0
1 2 20 "75-79 years" 346 2010 0
1 3 20 "75-79 years" 346 2010 0
1 1 22 "All ages" 346 2010 0
1 2 22 "All ages" 346 2010 0
1 3 22 "All ages" 346 2010 0
1 1 234 "75+ years" 346 2010 0
1 2 234 "75+ years" 346 2010 0
1 3 234 "75+ years" 346 2010 0
1 1 20 "75-79 years" 348 2010 25.038107
1 2 20 "75-79 years" 348 2010 14.910136
1 3 20 "75-79 years" 348 2010 39.94824
1 1 22 "All ages" 348 2010 3898.867
1 2 22 "All ages" 348 2010 1838.3585
1 3 22 "All ages" 348 2010 5737.226
1 1 20 "75-79 years" 297 2010 20062.254
1 2 20 "75-79 years" 297 2010 13640.828
1 3 20 "75-79 years" 297 2010 33703.082
1 1 22 "All ages" 297 2010 299455.63
1 2 22 "All ages" 297 2010 160457.6
1 3 22 "All ages" 297 2010 459913.2
1 1 234 "75+ years" 297 2010 38501.38
1 2 234 "75+ years" 297 2010 25151.5
1 3 234 "75+ years" 297 2010 63652.88
1 2 22 "All ages" 370 2010 1628.5184
1 3 22 "All ages" 370 2010 1628.5184
1 2 22 "All ages" 374 2010 1712.9108
1 3 22 "All ages" 374 2010 1712.9108
1 2 22 "All ages" 375 2010 6688.983
1 3 22 "All ages" 375 2010 6688.983
1 2 22 "All ages" 376 2010 2418.917
1 3 22 "All ages" 376 2010 2418.917
1 2 22 "All ages" 379 2010 9263.8955
1 3 22 "All ages" 379 2010 9263.8955
1 1 22 "All ages" 381 2010 138465.19
1 2 22 "All ages" 381 2010 113279.87
1 3 22 "All ages" 381 2010 251745.05
1 1 22 "All ages" 382 2010 73869.59
1 2 22 "All ages" 382 2010 54093.7
1 3 22 "All ages" 382 2010 127963.3
1 1 22 "All ages" 383 2010 23257.264
1 2 22 "All ages" 383 2010 23070.545
1 3 22 "All ages" 383 2010 46327.81
1 1 22 "All ages" 384 2010 17968.08
1 2 22 "All ages" 384 2010 16691.229
1 3 22 "All ages" 384 2010 34659.31
1 1 22 "All ages" 385 2010 100106.96
1 2 22 "All ages" 385 2010 86876.4
1 3 22 "All ages" 385 2010 186983.36
1 1 20 "75-79 years" 387 2010 305.7084
1 2 20 "75-79 years" 387 2010 282.0884
1 3 20 "75-79 years" 387 2010 587.79675
1 1 22 "All ages" 387 2010 13957.778
1 2 22 "All ages" 387 2010 22891.8
1 3 22 "All ages" 387 2010 36849.58
1 1 22 "All ages" 414 2010 34695.824
1 2 22 "All ages" 414 2010 30351.275
1 3 22 "All ages" 414 2010 65047.1
1 1 234 "75+ years" 414 2010 5826.542
1 2 234 "75+ years" 414 2010 5721.97
1 3 234 "75+ years" 414 2010 11548.512
end

The data set contains age groups from <1, 1-4, 5-9.... 74-75, 75-79 and then 75+ and all Ages. The all ages total value contains sum of all the ages. I want to re-group the age data as follows. <1, 1-4, 5-9.... and 74-75, 75-79 and 80+. As we have information of total (deaths for that particular age). We can subtract the deaths of 75-79 from 75+ age group we will get deaths for 80+. I want to do this. Can you please suggest.

[chintu red]

Solved. I have reshaped the entire data.

Thanks