How can I automatically fill certain cells using STATA 17?

[Nihat Mugurtay]

I have a time-series dataset, including more than 200 countries and years between 2000 and 2021. My dataset has three columns: Country, year, and value. I am trying to fill the country column without copy-paste and dragging the first item down. This will be panel data. In each unit (row), the line starts with the country name, as you see in the photo. Afghanistan -for instance- should be written next to all years. I have my country names which correspond to the year 2000. But, there should be country names for all the years between 2000 and 2021. I add a photo of my spreadsheet. I want to handle this in STATA. I add a photo of the excel document.

Thank you so much,

[William Lisowski]

replace countryname = countryname[_n-1] if missing(countryname)

[Jared Greathouse]

Please don't attach things, it doesn't help me help you.

Import the excel file into Stata and please give your example data using the dataex command. For us to provide meaningful feedback, you must provide your example data using the dataex command, the real data from an easily importable source (i.e., Github), or if none of these are possible, the equivalent of a toy example.
Otherwise, anything we say is simply a waste of time. Note, that I'm not trying to be mean in saying this, I'm saying this because if we can't see your dataset as you do with a minimal worked example, anything we suggest is just guesswork. The reason that I'm emphasizing this is because questions like this one likely have a relatively simple fix, but even simple fixes can be wildly overcomplicated without a minimal worked example of a dataset and code that you've tried to accomplish your task.

So please, provide us with your example data that encapsulates the problem and I'm more than okay with helping you solve this.

Crossed with #2: Another solution to this would (i think) be the carryforward command written by David Kantor.

[Nihat Mugurtay]

Dear William & Jared,

Thank you so much,

I carried out what William suggested. It worked.

Dear Jared, your remark is significant feedback for me. I am a newcomer and will be careful about your points.

Best,

[William Lisowski]

I am glad to hear that it worked. If I had been writing from my computer instead of my phone, I would have suggested several ways it might not have worked, including for example if the blank countrynames had space characters in them rather than being completely empty.

So let me include this tutorial in using the dataex command, not only in case it helps you, but also for the benefit of any newcomer who reads this topic sometime in the future.

The output of help dataex explains the command syntax and options.

By default dataex will output the first 100 observations, but this can be changed using if and in and the obs() option. The output of dataex will look something like the following.

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int(x1 x2 x3) float x4 int x5 byte x6
 4195 24 1   2 10 0
10371 16 3 3.5 17 0
 4647 28 3   2 11 0
...
 5079 24 4 2.5  8 1
 8129 21 4 2.5  8 1
 4296 21 3 2.5 16 1
end
label values x6 yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify
[/CODE]
------------------ copy up to and including the previous line ------------------

In your dataex output you will select the lines between, but not including, "copy starting from the next line" and "copy up to and including the previous line" and then paste that into your reply. The result presented in your post will look something like the following.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(x1 x2 x3) float x4 int x5 byte x6
 4195 24 1   2 10 0
10371 16 3 3.5 17 0
 4647 28 3   2 11 0
...
 5079 24 4 2.5  8 1
 8129 21 4 2.5  8 1
 4296 21 3 2.5 16 1
end
label values x6 yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify

What this example doesn't show is that a "missing" countryname would be presented as

""

while one with a single space character would be presented as

" "

and we can distinguish which you might have, leading to correct code, rather than making a guess.

[Nihat Mugurtay]

Dear William,

Your point is really remarkable and will be a caution for all of us.
Thank you again for solving my puzzle.

Best

[Nihat Mugurtay]

Dear William Lisowski,

I used what you recommended and it worked for my previous datasets. However, I have a problem with another dataset.

I have a new dataset below. In the previous datasets, the country name starts with the first year. Therefore, your code worked properly; however, in this new case, some country names are located in the middle, and when I use the way you said, for example, Albania takes the place of Algeria for certain years. I will be very happy if I solve the problem.

Jared Greathouse might be interested in it also.

* Example generated by -dataex-. For more info, type help dataex
clear
input str37 recipient int year float(amountgrant newid)
"Afghanistan"       2000 1113800.8 1
"Afghanistan"       2001 2446247.3 1
"Afghanistan"       2002  15609534 1
"Afghanistan"       2003  14994850 1
"Afghanistan"       2004  28665932 1
"Afghanistan"       2005  48972180 1
"Afghanistan"       2006  19458870 1
"Afghanistan"       2007  21895556 1
"Afghanistan"       2008  28806104 1
"Afghanistan"       2009   8707406 1
"Afghanistan"       2010  25351238 1
"Afghanistan"       2011  17174934 1
"Afghanistan"       2012    694219 1
""                  2013         . 1
"Afghanistan"       2014   2568095 1
"Afghanistan"       2015  26163968 1
"Afghanistan"       2016  45765568 1
"Afghanistan"       2017  88572696 1
""                  2000         . 2
"Africa, regional"  2001   4350806 2
""                  2002         . 2
""                  2003         . 2
"Africa, regional"  2004  238852.1 2
""                  2005         . 2
"Africa, regional"  2006 188416672 2
""                  2007         . 2
""                  2008         . 2
""                  2009         . 2
""                  2010         . 2
""                  2011         . 2
"Africa, regional"  2012 134370176 2
"Africa, regional"  2013  97166.19 2
"Africa, regional"  2014   5878883 2
"Africa, regional"  2015   8061848 2
"Africa, regional"  2016  36659420 2
""                  2017         . 2
"Albania"           2000   2673122 3
""                  2001         . 3
""                  2002         . 3
"Albania"           2003   1374388 3
"Albania"           2004   5955135 3
"Albania"           2005 2324775.3 3
""                  2006         . 3
"Albania"           2007         0 3
""                  2008         . 3
"Albania"           2009         0 3
"Albania"           2010 1232805.9 3
"Albania"           2011 1652099.4 3
"Albania"           2012  80723.14 3
"Albania"           2013         0 3
"Albania"           2014         0 3
"Albania"           2015 1564194.3 3
"Albania"           2016   1691729 3
"Albania"           2017   5005773 3
""                  2000         . 4
""                  2001         . 4
""                  2002         . 4
"Algeria"           2003 1277285.3 4
"Algeria"           2004 2388520.8 4
""                  2005         . 4
""                  2006         . 4
"Algeria"           2007  801245.9 4
""                  2008         . 4
"Algeria"           2009 1811110.3 4
"Algeria"           2010  57136568 4
""                  2011         . 4
"Algeria"           2012         0 4
""                  2013         . 4
""                  2014         . 4
"Algeria"           2015         0 4
"Algeria"           2016  17897466 4
"Algeria"           2017  29596978 4
""                  2000         . 5
""                  2001         . 5
""                  2002         . 5
"America, regional" 2003         0 5
""                  2004         . 5
""                  2005         . 5
""                  2006         . 5
""                  2007         . 5
""                  2008         . 5
""                  2009         . 5
""                  2010         . 5
""                  2011         . 5
""                  2012         . 5
""                  2013         . 5
""                  2014         . 5
""                  2015         . 5
""                  2016         . 5
"America, regional" 2017   5000000 5
""                  2000         . 6
"Angola"            2001   1309537 6
"Angola"            2002 3913893.5 6
""                  2003         . 6
""                  2004         . 6
"Angola"            2005 1884110.5 6
"Angola"            2006 1016265.5 6
"Angola"            2007  410568.2 6
""                  2008         . 6
"Angola"            2009         0 6
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 2538 observations
Use the count() option to list more

[William Lisowski]

If you sort within newid by recipient, the non-blank values will sort to the end of the group.

bysort newid (recipient): replace recipient = recipient[_N] if missing(recipient)
sort newid year

. list if newid==4, clean abbreviate(12)

       recipient   year   amountgrant   newid  
 55.               2000             .       4  
 56.               2001             .       4  
 57.               2002             .       4  
 58.     Algeria   2003       1277285       4  
 59.     Algeria   2004       2388521       4  
 60.               2005             .       4  
 61.               2006             .       4  
 62.     Algeria   2007      801245.9       4  
 63.               2008             .       4  
 64.     Algeria   2009       1811110       4  
 65.     Algeria   2010      5.71e+07       4  
 66.               2011             .       4  
 67.     Algeria   2012             0       4  
 68.               2013             .       4  
 69.               2014             .       4  
 70.     Algeria   2015             0       4  
 71.     Algeria   2016      1.79e+07       4  
 72.     Algeria   2017      2.96e+07       4  

. bysort newid (recipient): replace recipient = recipient[_N] if missing(recipient)
(44 real changes made)

. sort newid year

. list if newid==4, clean abbreviate(12)

       recipient   year   amountgrant   newid  
 55.     Algeria   2000             .       4  
 56.     Algeria   2001             .       4  
 57.     Algeria   2002             .       4  
 58.     Algeria   2003       1277285       4  
 59.     Algeria   2004       2388521       4  
 60.     Algeria   2005             .       4  
 61.     Algeria   2006             .       4  
 62.     Algeria   2007      801245.9       4  
 63.     Algeria   2008             .       4  
 64.     Algeria   2009       1811110       4  
 65.     Algeria   2010      5.71e+07       4  
 66.     Algeria   2011             .       4  
 67.     Algeria   2012             0       4  
 68.     Algeria   2013             .       4  
 69.     Algeria   2014             .       4  
 70.     Algeria   2015             0       4  
 71.     Algeria   2016      1.79e+07       4  
 72.     Algeria   2017      2.96e+07       4  

.

[Nihat Mugurtay]

Dear William Lisowski ,

Your solution has saved a great deal of time and helped a lot. The golden touch.


Thank you so much,

N.