add all month in the dataset

[Sa Fe]

Hi,

I have a dataset in which I have households' expenditures over some months. For instance,

household_id, year, month, price
1, 2020, 2, 10.00
1, 2020, 3, 12.00
1, 2019, 5, 7.00
2, 2018, 1, 5.00

The dataset records expenditures only for those months that any purchase occurs and does not include months in which no purchase occurs.

I want to add all months for each household_id like below:

household_id, year, month, price
1, 2020, 1, 0.00
1, 2020, 2, 10.00
1, 2020, 3, 12.00
1, 2020, 4, 0.00
...

How can I do this?

Thanks,

[Nick Cox]

From your example it seems that the data are already in the right form. Please show a data example that shows otherwise.

Please do read and act on https://www.statalist.org/forums/help#stata -- which explains how to give data examples (and how not to give them).

[Sa Fe]

* Example generated by -dataex-. To install: ssc install dataex
clear
input long household_code int year byte month double expense

2000179 2016 2  7.449999809265137
2003235 2015 3 1.9900000095367432
2004454 2019 3  7.989999771118164
2004493 2019 2  7.169999837875366
2004493 2014 2  4.190000057220459
2004493 2019 3                  4
2004493 2014 3  4.190000057220459
2004493 2020 3  4.289999961853027
2004632 2019 2 11.819999694824219
2004632 2019 3 11.859999656677246
2004694 2019 2  7.159999847412109
2004996 2017 2 11.979999542236328
2005694 2015 3  3.490000009536743
2006079 2015 2  6.489999771118164
2006079 2018 2  5.949999809265137
2006079 2019 3  5.670000076293945
2006409 2020 3  3.990000009536743
2006756 2015 2 3.9200000762939453
2007675 2020 2  51.85000038146973
2007968 2014 3  8.380000114440918
2009145 2015 2                3.5
2011895 2019 3  2.869999885559082
2012215 2018 3  3.299999952316284
2012463 2019 3  3.990000009536743
2013532 2020 2  3.950000047683716
2013635 2020 2 3.6700000762939453
2013635 2014 2 3.1700000762939453
2013635 2020 3 3.6700000762939453
2013635 2015 3 3.1700000762939453
2014093 2020 3  4.190000057220459
2016968 2017 3                2.5
2018417 2014 3  3.990000009536743
2021565 2014 3 3.4700000286102295
2023212 2020 3  3.990000009536743
2024135 2016 3  5.730000019073486
2024891 2020 2 3.6700000762939453
2024891 2017 3  4.989999771118164
2024891 2014 3  4.300000190734863
2025758 2020 2  6.980000019073486
2025758 2020 3  4.989999771118164
2026262 2017 3 3.3399999141693115
2029345 2020 3 20.569999933242798
2030710 2018 3  4.789999961853027
2031968 2016 2 15.279999732971191
2031968 2016 3 10.460000038146973
2032286 2014 2 3.6700000762939453
2033762 2014 3 3.6700000762939453
2036772 2018 3  3.990000009536743
2039412 2020 2 11.330000162124634
2039412 2017 2  7.340000152587891
2039412 2018 2 3.6700000762939453
2039412 2019 2  7.340000152587891
2039412 2018 3                3.5
2039412 2017 3 11.010000228881836
2039412 2020 3  7.340000152587891
2039412 2019 3 11.010000228881836

end

[Sa Fe]

From your example it seems that the data are already in the right form. Please show a data example that shows otherwise.

Please do read and act on https://www.statalist.org/forums/help#stata -- which explains how to give data examples (and how not to give them).

Just a quick note while you're taking a look at the above dataset. I want to add all years from 2014 to 2020 as well as all months for each household_id.

Thanks,

[Nick Cox]

I think I see what you want now. I was stupid there.

gen mdate = ym(year, month)
fillin household_code mdate 
replace expense = 0 if expense == .

[Sa Fe]

I think I see what you want now. I was stupid there.

gen mdate = ym(year, month)
fillin household_code mdate
replace expense = 0 if expense == .

Thanks,

How to extract back year and month variables separately from mdate?

[Nick Cox]

As you have year and month already as separate variables, why ask? But you can look at help datetime and quickly find solutions. Here are two each:

clear
set obs 12
gen mdate = ym(2021, 6) + _n
format mdate %tm

gen month2 = month(dofm(mdate))
gen month3 = 1 + mod(mdate, 12)

gen year2 = year(dofm(mdate))
gen year3 = 1960 + floor(mdate/12)

list, sepby(year2)

     +-------------------------------------------+
     |   mdate   month2   month3   year2   year3 |
     |-------------------------------------------|
  1. |  2021m7        7        7    2021    2021 |
  2. |  2021m8        8        8    2021    2021 |
  3. |  2021m9        9        9    2021    2021 |
  4. | 2021m10       10       10    2021    2021 |
  5. | 2021m11       11       11    2021    2021 |
  6. | 2021m12       12       12    2021    2021 |
     |-------------------------------------------|
  7. |  2022m1        1        1    2022    2022 |
  8. |  2022m2        2        2    2022    2022 |
  9. |  2022m3        3        3    2022    2022 |
 10. |  2022m4        4        4    2022    2022 |
 11. |  2022m5        5        5    2022    2022 |
 12. |  2022m6        6        6    2022    2022 |
     +-------------------------------------------+