Manipulating this data table - seems simple but I cannot think of a solution

[Adrian Fern]

So I think my question is best shown with a set of pictures.

This is what my current data looks like:

id	date	month #	growth value
1	June 1975	1	12
2	June 1975	1	.
3	June 1975	1	11
1	July 1975	2	.
2	July 1975	2	.
3	July 1975	2	.
1	Aug 1975	3	.
2	Aug 1975	3	.
3	Aug 1975	3	.

...	.................	............	...................
1	May 1976	12	.
2	May 1976	12	.
3	May 1976	12	.
1	June 1976	13	9
2	June 1976	13	8
3	June 1976	13	11
1	July 1976	14	.
2	July 1976	14	.
3	July 1976	14	.

So what you will notice is that For months 1 and 13, my column "growth value" has data, and everything inbetween or after is empty.

What I would like is the that months 2-12 use the growth value corresponding to month 1,
and months 14-26 use the growth value corresponding to month 13, and so on.

So my final data should look like this:

id	date	month #	growth value
1	June 1975	1	12
2	June 1975	1	.
3	June 1975	1	11
1	July 1975	2	12
2	July 1975	2	.
3	July 1975	2	11
1	Aug 1975	3	12
2	Aug 1975	3	.
3	Aug 1975	3	11

...	.................	............	...................
1	May 1976	12	12
2	May 1976	12	.
3	May 1976	12	11
1	June 1976	13	9
2	June 1976	13	8
3	June 1976	13	11
1	July 1976	14	9
2	July 1976	14	8
3	July 1976	14	11

What would be the best way of achieving this? In my mind, I have something like this planned:

If (growth value) is not empty, then (growth value + 1) = growth value
--> repeat 12 times until growth value + 11 is reached, and do it by id and month#.

I just wasn't sure how to code this. I would appreciate any help.

[Andrew Musau]

Welcome to Statalist. Please take the time to read through the FAQs, especially #12 on how to present data examples. The solution provided below relies on having a date variable recognized by Stata and it is not apparent from your example that this is the case. See

help datetime

on how to transform date strings to Stata dates. Here is your example above presented using dataex and including a date variable recognized by Stata.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float date byte(month growthvalue)
1 185  1 12
2 185  1  .
3 185  1 11
1 186  2  .
2 186  2  .
3 186  2  .
1 187  3  .
2 187  3  .
3 187  3  .
1 196 12  .
2 196 12  .
3 196 12  .
1 197 13  9
2 197 13  8
3 197 13 11
1 198 14  .
2 198 14  .
3 198 14  .
end

bys id (date): replace growthvalue=growthvalue[_n-1] if _n>1 & missing(growthvalue)
sort date month id
l

Res.:

. l

     +------------------------------+
     | id   date   month   growth~e |
     |------------------------------|
  1. |  1    185       1         12 |
  2. |  2    185       1          . |
  3. |  3    185       1         11 |
  4. |  1    186       2         12 |
  5. |  2    186       2          . |
     |------------------------------|
  6. |  3    186       2         11 |
  7. |  1    187       3         12 |
  8. |  2    187       3          . |
  9. |  3    187       3         11 |
 10. |  1    196      12         12 |
     |------------------------------|
 11. |  2    196      12          . |
 12. |  3    196      12         11 |
 13. |  1    197      13          9 |
 14. |  2    197      13          8 |
 15. |  3    197      13         11 |
     |------------------------------|
 16. |  1    198      14          9 |
 17. |  2    198      14          8 |
 18. |  3    198      14         11 |
     +------------------------------+