Transform annual data in quarterly data

[Ana Vasconcelos]

Hello,

I have a panel data with variables that are annual. I want to create a new variable in a quarterly frequency and keeping the sames annual values for each period.
For example, I have the annual value (in 2015) for land area=27500. I want to create a variable in a quarterly frequency using this value:
land_area_q1=27500;
land_area_q2=27500;
land area_q3=27500
land_area_q4=27500.

Can you help me with the code?

Thank you in advance!

[Nick Cox]

This makes most sense when you have two datasets, one yearly and one quarterly, and then the answer is not to do this, but to merge 1:m

Otherwise here is a worked example.

 
help expand 

* example 
webuse grunfeld
expand 4
bysort company year : gen qdate = yq(year, _n)
format qdate %tq

l if company == 1, sepby(year)

     +-----------------------------------------------------------+
     | company   year   invest   mvalue   kstock   time    qdate |
     |-----------------------------------------------------------|
  1. |       1   1935    317.6   3078.5      2.8      1   1935q1 |
  2. |       1   1935    317.6   3078.5      2.8      1   1935q2 |
  3. |       1   1935    317.6   3078.5      2.8      1   1935q3 |
  4. |       1   1935    317.6   3078.5      2.8      1   1935q4 |
     |-----------------------------------------------------------|
  5. |       1   1936    391.8   4661.7     52.6      2   1936q1 |
  6. |       1   1936    391.8   4661.7     52.6      2   1936q2 |
  7. |       1   1936    391.8   4661.7     52.6      2   1936q3 |
  8. |       1   1936    391.8   4661.7     52.6      2   1936q4 |
     |-----------------------------------------------------------|
  9. |       1   1937    410.6   5387.1    156.9      3   1937q1 |
 10. |       1   1937    410.6   5387.1    156.9      3   1937q2 |
 11. |       1   1937    410.6   5387.1    156.9      3   1937q3 |
 12. |       1   1937    410.6   5387.1    156.9      3   1937q4 |
     |-----------------------------------------------------------|
 13. |       1   1938    257.7   2792.2    209.2      4   1938q1 |
 14. |       1   1938    257.7   2792.2    209.2      4   1938q2 |
 15. |       1   1938    257.7   2792.2    209.2      4   1938q3 |
 16. |       1   1938    257.7   2792.2    209.2      4   1938q4 |
     |-----------------------------------------------------------|
 17. |       1   1939    330.8   4313.2    203.4      5   1939q1 |
 18. |       1   1939    330.8   4313.2    203.4      5   1939q2 |
 19. |       1   1939    330.8   4313.2    203.4      5   1939q3 |
 20. |       1   1939    330.8   4313.2    203.4      5   1939q4 |
     |-----------------------------------------------------------|
 21. |       1   1940    461.2   4643.9    207.2      6   1940q1 |
 22. |       1   1940    461.2   4643.9    207.2      6   1940q2 |
 23. |       1   1940    461.2   4643.9    207.2      6   1940q3 |
 24. |       1   1940    461.2   4643.9    207.2      6   1940q4 |
     |-----------------------------------------------------------|
 25. |       1   1941      512   4551.2    255.2      7   1941q1 |
 26. |       1   1941      512   4551.2    255.2      7   1941q2 |
 27. |       1   1941      512   4551.2    255.2      7   1941q3 |
 28. |       1   1941      512   4551.2    255.2      7   1941q4 |
     |-----------------------------------------------------------|
 29. |       1   1942      448   3244.1    303.7      8   1942q1 |
 30. |       1   1942      448   3244.1    303.7      8   1942q2 |
 31. |       1   1942      448   3244.1    303.7      8   1942q3 |
 32. |       1   1942      448   3244.1    303.7      8   1942q4 |
     |-----------------------------------------------------------|
 33. |       1   1943    499.6   4053.7    264.1      9   1943q1 |
 34. |       1   1943    499.6   4053.7    264.1      9   1943q2 |
 35. |       1   1943    499.6   4053.7    264.1      9   1943q3 |
 36. |       1   1943    499.6   4053.7    264.1      9   1943q4 |
     |-----------------------------------------------------------|
 37. |       1   1944    547.5   4379.3    201.6     10   1944q1 |
 38. |       1   1944    547.5   4379.3    201.6     10   1944q2 |
 39. |       1   1944    547.5   4379.3    201.6     10   1944q3 |
 40. |       1   1944    547.5   4379.3    201.6     10   1944q4 |
     |-----------------------------------------------------------|
 41. |       1   1945    561.2   4840.9      265     11   1945q1 |
 42. |       1   1945    561.2   4840.9      265     11   1945q2 |
 43. |       1   1945    561.2   4840.9      265     11   1945q3 |
 44. |       1   1945    561.2   4840.9      265     11   1945q4 |
     |-----------------------------------------------------------|
 45. |       1   1946    688.1   4900.9    402.2     12   1946q1 |
 46. |       1   1946    688.1   4900.9    402.2     12   1946q2 |
 47. |       1   1946    688.1   4900.9    402.2     12   1946q3 |
 48. |       1   1946    688.1   4900.9    402.2     12   1946q4 |
     |-----------------------------------------------------------|
 49. |       1   1947    568.9   3526.5    761.5     13   1947q1 |
 50. |       1   1947    568.9   3526.5    761.5     13   1947q2 |
 51. |       1   1947    568.9   3526.5    761.5     13   1947q3 |
 52. |       1   1947    568.9   3526.5    761.5     13   1947q4 |
     |-----------------------------------------------------------|
 53. |       1   1948    529.2   3254.7    922.4     14   1948q1 |
 54. |       1   1948    529.2   3254.7    922.4     14   1948q2 |
 55. |       1   1948    529.2   3254.7    922.4     14   1948q3 |
 56. |       1   1948    529.2   3254.7    922.4     14   1948q4 |
     |-----------------------------------------------------------|
 57. |       1   1949    555.1   3700.2   1020.1     15   1949q1 |
 58. |       1   1949    555.1   3700.2   1020.1     15   1949q2 |
 59. |       1   1949    555.1   3700.2   1020.1     15   1949q3 |
 60. |       1   1949    555.1   3700.2   1020.1     15   1949q4 |
     |-----------------------------------------------------------|
 61. |       1   1950    642.9   3755.6     1099     16   1950q1 |
 62. |       1   1950    642.9   3755.6     1099     16   1950q2 |
 63. |       1   1950    642.9   3755.6     1099     16   1950q3 |
 64. |       1   1950    642.9   3755.6     1099     16   1950q4 |
     |-----------------------------------------------------------|
 65. |       1   1951    755.9     4833   1207.7     17   1951q1 |
 66. |       1   1951    755.9     4833   1207.7     17   1951q2 |
 67. |       1   1951    755.9     4833   1207.7     17   1951q3 |
 68. |       1   1951    755.9     4833   1207.7     17   1951q4 |
     |-----------------------------------------------------------|
 69. |       1   1952    891.2   4924.9   1430.5     18   1952q1 |
 70. |       1   1952    891.2   4924.9   1430.5     18   1952q2 |
 71. |       1   1952    891.2   4924.9   1430.5     18   1952q3 |
 72. |       1   1952    891.2   4924.9   1430.5     18   1952q4 |
     |-----------------------------------------------------------|
 73. |       1   1953   1304.4   6241.7   1777.3     19   1953q1 |
 74. |       1   1953   1304.4   6241.7   1777.3     19   1953q2 |
 75. |       1   1953   1304.4   6241.7   1777.3     19   1953q3 |
 76. |       1   1953   1304.4   6241.7   1777.3     19   1953q4 |
     |-----------------------------------------------------------|
 77. |       1   1954   1486.7   5593.6   2226.3     20   1954q1 |
 78. |       1   1954   1486.7   5593.6   2226.3     20   1954q2 |
 79. |       1   1954   1486.7   5593.6   2226.3     20   1954q3 |
 80. |       1   1954   1486.7   5593.6   2226.3     20   1954q4 |
     +-----------------------------------------------------------+

.


If you don't have panel data the commands reduce to some variant on

expand 4
bysort year : gen qdate = yq(year, _n)
format qdate %tq

[Ana Vasconcelos]

Hello,

Thank you for your suggestion but I'm not being able to transform the data. What I have a variable that is annual and I want to replicate that value 4 time...that corresponds to the quarterly observations.
Can you help me with the code?

Thak you very much

[Nick Cox]

That's precisely what the code in #2 does. Did you read in the example dataset, look at it and then track what happened?

[Ana Vasconcelos]

Thank you for your answer Nick. I used the code in #2 and it worked!
Thank you

[Naimat Wazir]

Dear Nick Cox,

I have panel dataset which I need to convert into quarterly data, the reason for the transformation of the data is that the variables of interest is quarterly available. Importantly, I tried your other suggestions as well but it didn't work for panel data and within year variation in dataset.

kindly, recode and re-illustrate the above given dataset in terms of panel data and within year variation of the data.

Thank you!

[Nick Cox]

Sorry, but #6 is impossible for me to answer. Code you don't show us that doesn't work on data we can't see -- and you want me to give different code that will work for you? I don't have different ideas on how to do it. The possibilities I know are to use merge and to use expand.

So, please back up and show enough data and enough code and enough explanation of what you think is wrong for people to see what is happening.

[Naimat Wazir]

Nick Cox Thank your for your response!
I have the following data, not complete dataset but just for instance, two country and years 2004-2015.
I am trying to convert some yearly data to quarterly for sound reason.

Economy	iid	Year	Broad money
AFG	1	2004
AFG	1	2005
AFG	1	2006	23.3438645
AFG	1	2007	23.8336644
AFG	1	2008	29.5122808
AFG	1	2009	33.060957
AFG	1	2010
AFG	1	2011	36.6242278
AFG	1	2012	32.9136923
AFG	1	2013	32.6820169
AFG	1	2014	34.2095968
AFG	1	2015
AFG	1	2016	37.2228187
AFG	1	2017
AFG	1	2018	36.6074939
AFG	1	2019	34.9592143
AFG	1	2020	37.2094634
AFG	1	2021
Pak	2	2004	68.2203667
Pak	2	2005	71.4176324
Pak	2	2006	76.365678
Pak	2	2007	78.4658757
Pak	2	2008	75.4805326
Pak	2	2009	76.1817095
Pak	2	2010	79.0774778
Pak	2	2011	82.2797365
Pak	2	2012
Pak	2	2013	85.1062884
Pak	2	2014	85.6508075
Pak	2	2015	84.7921679
Pak	2	2016	85.8049588
Pak	2	2017	81.7034202
Pak	2	2018	77.2350496
Pak	2	2019	77.9394365
Pak	2	2020	88.6205306
Pak	2	2021

I tried the following code as you suggested but with some changes I made for the sake of panel

xtset iid year
expand 4

sort year iid
/// it will sort data properly

bysort year iid : gen qdate = yq(year, _n)
format qdate %tq

by year iid: replace broadmoney = . if _n > 1

list, sepby(year)

gen logBM = log(broadmoney)

ipolate broadmoney qdate, gen(linear) epolate
ipolate logBM qdate, gen(log) epolate
replace log = exp(log)
format linear log %4.1f
list, sepby(year)

The results are as follows,
+---------------------------------------------------------------------+
economy iid year broadm~y qdate logBM linear log
---------------------------------------------------------------------
1. AFG 1 2004 . 2004q1 . 68.2 68.2
2. AFG 1 2004 . 2004q2 . 69.0 69.0
3. AFG 1 2004 . 2004q3 . 69.8 69.8
4. AFG 1 2004 . 2004q4 . 70.6 70.6
5. Pak 2 2004 68.2204 2004q1 4.222743 68.2 68.2
6. Pak 2 2004 . 2004q2 . 69.0 69.0
7. Pak 2 2004 . 2004q3 . 69.8 69.8
8. Pak 2 2004 . 2004q4 . 70.6 70.6
---------------------------------------------------------------------
9. AFG 1 2005 . 2005q1 . 71.4 71.4
10. AFG 1 2005 . 2005q2 . 66.0 62.6
11. AFG 1 2005 . 2005q3 . 60.6 54.9
12. AFG 1 2005 . 2005q4 . 55.2 48.2
13. Pak 2 2005 71.4176 2005q1 4.268545 71.4 71.4
14. Pak 2 2005 . 2005q2 . 66.0 62.6
15. Pak 2 2005 . 2005q3 . 60.6 54.9
16. Pak 2 2005 . 2005q4 . 55.2 48.2
---------------------------------------------------------------------
17. AFG 1 2006 23.3439 2006q1 3.150334 49.9 42.2
18. AFG 1 2006 . 2006q2 . 50.2 42.5
19. AFG 1 2006 . 2006q3 . 50.5 42.7
20. AFG 1 2006 . 2006q4 . 50.8 43.0
21. Pak 2 2006 76.3657 2006q1 4.335533 49.9 42.2
22. Pak 2 2006 . 2006q2 . 50.2 42.5
23. Pak 2 2006 . 2006q3 . 50.5 42.7
24. Pak 2 2006 . 2006q4 . 50.8 43.0
---------------------------------------------------------------------
25. AFG 1 2007 23.8337 2007q1 3.171099 51.1 43.2
26. AFG 1 2007 . 2007q2 . 51.5 44.2
27. AFG 1 2007 . 2007q3 . 51.8 45.2
28. AFG 1 2007 . 2007q4 . 52.2 46.2
29. Pak 2 2007 78.4659 2007q1 4.362664 51.1 43.2
30. Pak 2 2007 . 2007q2 . 51.5 44.2
31. Pak 2 2007 . 2007q3 . 51.8 45.2
32. Pak 2 2007 . 2007q4 . 52.2 46.2
---------------------------------------------------------------------
33. AFG 1 2008 29.5123 2008q1 3.384806 52.5 47.2
34. AFG 1 2008 . 2008q2 . 53.0 47.9
35. AFG 1 2008 . 2008q3 . 53.6 48.7
36. AFG 1 2008 . 2008q4 . 54.1 49.4
37. Pak 2 2008 75.4805 2008q1 4.323875 52.5 47.2
38. Pak 2 2008 . 2008q2 . 53.0 47.9
39. Pak 2 2008 . 2008q3 . 53.6 48.7
40. Pak 2 2008 . 2008q4 . 54.1 49.4
---------------------------------------------------------------------
41. AFG 1 2009 33.061 2009q1 3.498353 54.6 50.2
42. AFG 1 2009 . 2009q2 . 60.7 56.2
43. AFG 1 2009 . 2009q3 . 66.8 63.0
44. AFG 1 2009 . 2009q4 . 73.0 70.6
45. Pak 2 2009 76.1817 2009q1 4.333121 54.6 50.2
46. Pak 2 2009 . 2009q2 . 60.7 56.2
47. Pak 2 2009 . 2009q3 . 66.8 63.0
48. Pak 2 2009 . 2009q4 . 73.0 70.6
---------------------------------------------------------------------
49. AFG 1 2010 . 2010q1 . 79.1 79.1
50. AFG 1 2010 . 2010q2 . 74.2 72.2
51. AFG 1 2010 . 2010q3 . 69.3 65.9
52. AFG 1 2010 . 2010q4 . 64.4 60.1
53. Pak 2 2010 79.0775 2010q1 4.370428 79.1 79.1
54. Pak 2 2010 . 2010q2 . 74.2 72.2
55. Pak 2 2010 . 2010q3 . 69.3 65.9
56. Pak 2 2010 . 2010q4 . 64.4 60.1
---------------------------------------------------------------------
57. AFG 1 2011 36.6242 2011q1 3.60071 59.5 54.9
58. AFG 1 2011 . 2011q2 . 52.8 48.3
59. AFG 1 2011 . 2011q3 . 46.2 42.5
60. AFG 1 2011 . 2011q4 . 39.5 37.4
61. Pak 2 2011 82.2797 2011q1 4.410125 59.5 54.9
62. Pak 2 2011 . 2011q2 . 52.8 48.3
63. Pak 2 2011 . 2011q3 . 46.2 42.5
64. Pak 2 2011 . 2011q4 . 39.5 37.4
---------------------------------------------------------------------
65. AFG 1 2012 32.9137 2012q1 3.493889 32.9 32.9
66. AFG 1 2012 . 2012q2 . 39.4 37.0
67. AFG 1 2012 . 2012q3 . 45.9 41.7
68. AFG 1 2012 . 2012q4 . 52.4 46.9
69. Pak 2 2012 . 2012q1 . 32.9 32.9
70. Pak 2 2012 . 2012q2 . 39.4 37.0
71. Pak 2 2012 . 2012q3 . 45.9 41.7
72. Pak 2 2012 . 2012q4 . 52.4 46.9
---------------------------------------------------------------------
73. AFG 1 2013 32.682 2013q1 3.486825 58.9 52.7
74. AFG 1 2013 . 2013q2 . 59.2 53.1
75. AFG 1 2013 . 2013q3 . 59.4 53.4
76. AFG 1 2013 . 2013q4 . 59.7 53.8
77. Pak 2 2013 85.1063 2013q1 4.443901 58.9 52.7
78. Pak 2 2013 . 2013q2 . 59.2 53.1
79. Pak 2 2013 . 2013q3 . 59.4 53.4
80. Pak 2 2013 . 2013q4 . 59.7 53.8
---------------------------------------------------------------------
81. AFG 1 2014 34.2096 2014q1 3.532506 59.9 54.1
82. AFG 1 2014 . 2014q2 . 60.2 54.5
83. AFG 1 2014 . 2014q3 . 60.4 54.8
84. AFG 1 2014 . 2014q4 . 60.7 55.2
85. Pak 2 2014 85.6508 2014q1 4.450279 59.9 54.1
86. Pak 2 2014 . 2014q2 . 60.2 54.5
87. Pak 2 2014 . 2014q3 . 60.4 54.8
88. Pak 2 2014 . 2014q4 . 60.7 55.2
---------------------------------------------------------------------
89. AFG 1 2015 . 2015q1 . 61.0 55.6
90. AFG 1 2015 . 2015q2 . 61.2 55.9
91. AFG 1 2015 . 2015q3 . 61.5 56.3
92. AFG 1 2015 . 2015q4 . 61.7 56.7
+---------------------------------------------------------------------+

Two concerns: how to deal with missing data in given dataset, because values are generated for particular year not having any value e.g. AFG 2004 ?
Second, the generated values are deviated largely from the original values such as AFG 2006q1 generated values is 42.2 but the original values is 23.34.

Pls your comments and further guidance
Thank you!

[Nick Cox]

Thanks for the detail. What is most obvious here is that your interpolation mixes economies together, which I doubt makes economic sense. You need the option by(economy) on each ipolate call.

[Naimat Wazir]

Here is the results of by (economy) option

ipolate broadmoney qdate, gen(linear) epolate by (economy)

+---------------------------------------------------------------------+
| economy iid year broadm~y qdate logBM linear log |
|---------------------------------------------------------------------|
1. | AFG 1 2004 . 2004q1 . 22.4 68.2 |
2. | AFG 1 2004 . 2004q2 . 22.5 69.0 |
3. | AFG 1 2004 . 2004q3 . 22.6 69.8 |
4. | AFG 1 2004 . 2004q4 . 22.7 70.6 |
5. | Pak 2 2004 68.2204 2004q1 4.222743 68.2 68.2 |
6. | Pak 2 2004 . 2004q2 . 69.0 69.0 |
7. | Pak 2 2004 . 2004q3 . 69.8 69.8 |
8. | Pak 2 2004 . 2004q4 . 70.6 70.6 |
|---------------------------------------------------------------------|
9. | AFG 1 2005 . 2005q1 . 22.9 71.4 |
10. | AFG 1 2005 . 2005q2 . 23.0 62.6 |
11. | AFG 1 2005 . 2005q3 . 23.1 54.9 |
12. | AFG 1 2005 . 2005q4 . 23.2 48.2 |
13. | Pak 2 2005 71.4176 2005q1 4.268545 71.4 71.4 |
14. | Pak 2 2005 . 2005q2 . 72.7 62.6 |
15. | Pak 2 2005 . 2005q3 . 73.9 54.9 |
16. | Pak 2 2005 . 2005q4 . 75.1 48.2 |
|---------------------------------------------------------------------|
17. | AFG 1 2006 23.3439 2006q1 3.150334 23.3 42.2 |
18. | AFG 1 2006 . 2006q2 . 23.5 42.5 |
19. | AFG 1 2006 . 2006q3 . 23.6 42.7 |
20. | AFG 1 2006 . 2006q4 . 23.7 43.0 |
21. | Pak 2 2006 76.3657 2006q1 4.335533 76.4 42.2 |
22. | Pak 2 2006 . 2006q2 . 76.9 42.5 |
23. | Pak 2 2006 . 2006q3 . 77.4 42.7 |
24. | Pak 2 2006 . 2006q4 . 77.9 43.0 |
|---------------------------------------------------------------------|
25. | AFG 1 2007 23.8337 2007q1 3.171099 23.8 43.2 |
26. | AFG 1 2007 . 2007q2 . 25.3 44.2 |
27. | AFG 1 2007 . 2007q3 . 26.7 45.2 |
28. | AFG 1 2007 . 2007q4 . 28.1 46.2 |
29. | Pak 2 2007 78.4659 2007q1 4.362664 78.5 43.2 |
30. | Pak 2 2007 . 2007q2 . 77.7 44.2 |
31. | Pak 2 2007 . 2007q3 . 77.0 45.2 |
32. | Pak 2 2007 . 2007q4 . 76.2 46.2 |
|---------------------------------------------------------------------|
33. | AFG 1 2008 29.5123 2008q1 3.384806 29.5 47.2 |
34. | AFG 1 2008 . 2008q2 . 30.4 47.9 |
35. | AFG 1 2008 . 2008q3 . 31.3 48.7 |
36. | AFG 1 2008 . 2008q4 . 32.2 49.4 |
37. | Pak 2 2008 75.4805 2008q1 4.323875 75.5 47.2 |
38. | Pak 2 2008 . 2008q2 . 75.7 47.9 |
39. | Pak 2 2008 . 2008q3 . 75.8 48.7 |
40. | Pak 2 2008 . 2008q4 . 76.0 49.4 |
|---------------------------------------------------------------------|
41. | AFG 1 2009 33.061 2009q1 3.498353 33.1 50.2 |
42. | AFG 1 2009 . 2009q2 . 33.5 56.2 |
43. | AFG 1 2009 . 2009q3 . 34.0 63.0 |
44. | AFG 1 2009 . 2009q4 . 34.4 70.6 |
45. | Pak 2 2009 76.1817 2009q1 4.333121 76.2 50.2 |
46. | Pak 2 2009 . 2009q2 . 76.9 56.2 |
47. | Pak 2 2009 . 2009q3 . 77.6 63.0 |
48. | Pak 2 2009 . 2009q4 . 78.4 70.6 |
|---------------------------------------------------------------------|
49. | AFG 1 2010 . 2010q1 . 34.8 79.1 |
50. | AFG 1 2010 . 2010q2 . 35.3 72.2 |
51. | AFG 1 2010 . 2010q3 . 35.7 65.9 |
52. | AFG 1 2010 . 2010q4 . 36.2 60.1 |
53. | Pak 2 2010 79.0775 2010q1 4.370428 79.1 79.1 |
54. | Pak 2 2010 . 2010q2 . 79.9 72.2 |
55. | Pak 2 2010 . 2010q3 . 80.7 65.9 |
56. | Pak 2 2010 . 2010q4 . 81.5 60.1 |
|---------------------------------------------------------------------|
57. | AFG 1 2011 36.6242 2011q1 3.60071 36.6 54.9 |
58. | AFG 1 2011 . 2011q2 . 35.7 48.3 |
59. | AFG 1 2011 . 2011q3 . 34.8 42.5 |
60. | AFG 1 2011 . 2011q4 . 33.8 37.4 |
61. | Pak 2 2011 82.2797 2011q1 4.410125 82.3 54.9 |
62. | Pak 2 2011 . 2011q2 . 82.6 48.3 |
63. | Pak 2 2011 . 2011q3 . 83.0 42.5 |
64. | Pak 2 2011 . 2011q4 . 83.3 37.4 |
|---------------------------------------------------------------------|
65. | AFG 1 2012 32.9137 2012q1 3.493889 32.9 32.9 |
66. | AFG 1 2012 . 2012q2 . 32.9 37.0 |
67. | AFG 1 2012 . 2012q3 . 32.8 41.7 |
68. | AFG 1 2012 . 2012q4 . 32.7 46.9 |
69. | Pak 2 2012 . 2012q1 . 83.7 32.9 |
70. | Pak 2 2012 . 2012q2 . 84.0 37.0 |
71. | Pak 2 2012 . 2012q3 . 84.4 41.7 |
72. | Pak 2 2012 . 2012q4 . 84.8 46.9 |
|---------------------------------------------------------------------|
73. | AFG 1 2013 32.682 2013q1 3.486825 32.7 52.7 |
74. | AFG 1 2013 . 2013q2 . 33.1 53.1 |
75. | AFG 1 2013 . 2013q3 . 33.4 53.4 |
76. | AFG 1 2013 . 2013q4 . 33.8 53.8 |
77. | Pak 2 2013 85.1063 2013q1 4.443901 85.1 52.7 |
78. | Pak 2 2013 . 2013q2 . 85.2 53.1 |
79. | Pak 2 2013 . 2013q3 . 85.4 53.4 |
80. | Pak 2 2013 . 2013q4 . 85.5 53.8 |
|---------------------------------------------------------------------|
81. | AFG 1 2014 34.2096 2014q1 3.532506 34.2 54.1 |
82. | AFG 1 2014 . 2014q2 . 34.6 54.5 |
83. | AFG 1 2014 . 2014q3 . 35.0 54.8 |
84. | AFG 1 2014 . 2014q4 . 35.4 55.2 |
85. | Pak 2 2014 85.6508 2014q1 4.450279 85.7 54.1 |
86. | Pak 2 2014 . 2014q2 . 85.8 54.5 |
87. | Pak 2 2014 . 2014q3 . 85.9 54.8 |
88. | Pak 2 2014 . 2014q4 . 86.1 55.2 |
|---------------------------------------------------------------------|
89. | AFG 1 2015 . 2015q1 . 35.7 55.6 |
90. | AFG 1 2015 . 2015q2 . 36.1 55.9 |
91. | AFG 1 2015 . 2015q3 . 36.5 56.3 |
92. | AFG 1 2015 . 2015q4 . 36.9 56.7 |
+---------------------------------------------------------------------+


How to deal with the missing value not available for particular years?
secondly, what variable is the outcomes (final) variable: log or linear ?

Thanks!

[Nick Cox]

Please use CODE delimiters as in most of the rest of the thread.

I did this and the results look fair,

*

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 economy byte iid int year float broadmoney
"AFG" 1 2004         .
"AFG" 1 2005         .
"AFG" 1 2006 23.343864
"AFG" 1 2007 23.833664
"AFG" 1 2008  29.51228
"AFG" 1 2009  33.06096
"AFG" 1 2010         .
"AFG" 1 2011  36.62423
"AFG" 1 2012 32.913692
"AFG" 1 2013  32.68202
"AFG" 1 2014   34.2096
"AFG" 1 2015         .
"AFG" 1 2016  37.22282
"AFG" 1 2017         .
"AFG" 1 2018 36.607494
"AFG" 1 2019 34.959213
"AFG" 1 2020 37.209465
"AFG" 1 2021         .
"Pak" 2 2004  68.22037
"Pak" 2 2005  71.41763
"Pak" 2 2006  76.36568
"Pak" 2 2007  78.46587
"Pak" 2 2008  75.48053
"Pak" 2 2009  76.18171
"Pak" 2 2010  79.07748
"Pak" 2 2011  82.27974
"Pak" 2 2012         .
"Pak" 2 2013  85.10629
"Pak" 2 2014  85.65081
"Pak" 2 2015  84.79217
"Pak" 2 2016  85.80496
"Pak" 2 2017  81.70342
"Pak" 2 2018  77.23505
"Pak" 2 2019  77.93944
"Pak" 2 2020  88.62053
"Pak" 2 2021         .
end

expand 4
bysort year iid : gen qdate = yq(year, _n)
format qdate %tq

by year iid: replace broadmoney = . if _n > 1

gen logBM = log(broadmoney)

ipolate broadmoney qdate, gen(linear) epolate by(economy)
ipolate logBM qdate, gen(log) epolate by(economy)
replace log = exp(log)
format linear log %4.1f

scatter broadmoney qdate || line linear log qdate, by(economy)

linear and log are similar, but choose on whatever grounds you like. If your full problem includes other countries, the advice might differ.

I don't understand the question about missing values. You're already using epolate to extrapolate, and there might be doubts about whether that is a good idea, but those are economic questions for you as researcher (and the rest of your team).

[Naimat Wazir]

Just for further clarity, by missing values I mean:
Refer to the table I provided there are some years do not have any values e.g. for AFG years 2004, 2005, 2010 etc have missing data. In case I do not want to extrapolate those missing years values. Say I want only to extrapolate years having values?

Thanks a lot Nick Cox!

[Nick Cox]

You can always choose not to extrapolate or to specify an if qualifier or to interpolate and then ignore some of the results. It is up to you.

[Naimat Wazir]

It's working nicely, Thank you Nick Cox!