auto fill the next row with current row value if next row is empty, no group id available

[ji zhou]

Hello, I have the following dataset:

input str10 taxid str20 employer str10 degree number
"123" "google" "BS" 2000
"" "" "MS" 2000
"" "" "PHD" 1000
"456" "amazon" "BS" 1000
"" "" "MS" 1500
"" "" "PHD" 2100
"789" "EY" "BS" 200
"" "" "MS" 150
end

I want to fill out the taxid by copying the value in the current row to the next row if the next row is empty. If the next row is not empty, stop copying. The output I'd like to have is

input str10 taxid str20 employer str10 degree number
"123" "google" "BS" 2000
"123" "google" "MS" 2000
"123" "google" "PHD" 1000
"456" "amazon" "BS" 1000
"456" "amazon" "MS" 1500
"456" "amazon" "PHD" 2100
"789" "EY" "BS" 200
"789" "EY" "MS" 150
end

Thank you for your advice!

[Clyde Schechter]

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

[Nick Cox]

For discussion, elaboration and variations of Clyde's code see e.g.

https://www.stata.com/support/faqs/d...issing-values/

https://www.statalist.org/forums/for...-interpolation

[ji zhou]

Thanks Clyde! The code works perfect.

I was using this code before, but failed. Could you please point out to me why the code was wrong? Thanks

My failed code:

    replace taxid=taxid[_n-1] if taxid=="" & taxid[_n-1]!=""

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

[Clyde Schechter]

I don't know why that code didn't work for you. It works fine on my setup:

. clear

. input str10 taxid str20 employer str10 degree number

          taxid              employer      degree     number
  1. "123" "google" "BS" 2000
  2. "" "google" "MS" 2000
  3. "" "google" "PHD" 1000
  4. "456" "amazon" "BS" 1000
  5. "" "amazon" "MS" 1500
  6. "" "amazon" "PHD" 2100
  7. "789" "EY" "BS" 200
  8. "" "EY" "MS" 150
  9. end 

. 
. replace taxid=taxid[_n-1] if taxid=="" & taxid[_n-1]!=""
(5 real changes made)

. 
. list, noobs clean

    taxid   employer   degree   number  
      123     google       BS     2000  
      123     google       MS     2000  
      123     google      PHD     1000  
      456     amazon       BS     1000  
      456     amazon       MS     1500  
      456     amazon      PHD     2100  
      789         EY       BS      200  
      789         EY       MS      150

[ji zhou]

I am answering my own question. I just saw the "empty" cells imported from excel were not technically missing in Stata, perhaps due to some format issues? I replace those cells to be missing and the code worked.

[ji zhou]

Thank you for taking a look!

I don't know why that code didn't work for you. It works fine on my setup:

. clear

. input str10 taxid str20 employer str10 degree number

taxid employer degree number
1. "123" "google" "BS" 2000
2. "" "google" "MS" 2000
3. "" "google" "PHD" 1000
4. "456" "amazon" "BS" 1000
5. "" "amazon" "MS" 1500
6. "" "amazon" "PHD" 2100
7. "789" "EY" "BS" 200
8. "" "EY" "MS" 150
9. end

.
. replace taxid=taxid[_n-1] if taxid=="" & taxid[_n-1]!=""
(5 real changes made)

.
. list, noobs clean

taxid employer degree number
123 google BS 2000
123 google MS 2000
123 google PHD 1000
456 amazon BS 1000
456 amazon MS 1500
456 amazon PHD 2100
789 EY BS 200
789 EY MS 150