Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I backwards fill the missing values with the non-missing value of the variable as per the groups within the sample?

    How can I backwards fill the missing values with the non-missing value of the variable as per the groups within the sample?

    I have the following data:
    I want to fill up the missing value with the value that is not missing. But there are IDs for which no date fillup is needed as either all the date values are missing or all date values are filled up.
    ID Date
    1 .
    1 1sep2020
    2 .
    2 .
    2 3sep2021
    3 .
    3 4sep2021
    4 .
    4 .
    4 1sep2020
    5 .
    5 5sep2021
    6 3sep2022
    6 3sep2022
    6 3sep2022
    7 .
    7 .
    I want the output like this:
    ID Date
    1 1sep2020
    1 1sep2020
    2 3sep2021
    2 3sep2021
    2 3sep2021
    3 4sep2021
    3 4sep2021
    4 1sep2020
    4 1sep2020
    4 1sep2020
    5 5sep2021
    5 5sep2021
    6 3sep2022
    6 3sep2022
    6 3sep2022
    7 .
    7 .
    I tried several codes:
    while, for and if loop, reverse id sorting, ipolate but none of them work
    I also tried this code: bysort id: replace date = date[_n+1] if missing(date) but it only fills up one previous missing value and not the rest.

  • #2
    Code:
    * Example generated by -dataex-. For more info, type help dataex
    clear
    input byte id float date
    1     .
    1 22159
    2     .
    2     .
    2 22526
    3     .
    3 22527
    4     .
    4     .
    4 22159
    5     .
    5 22528
    6 22891
    6 22891
    6 22891
    7     .
    7     .
    end
    format %td date
    
    bys id (date): replace date= date[1]
    Res.:

    Code:
    . l, sepby(id)
    
         +----------------+
         | id        date |
         |----------------|
      1. |  1   01sep2020 |
      2. |  1   01sep2020 |
         |----------------|
      3. |  2   03sep2021 |
      4. |  2   03sep2021 |
      5. |  2   03sep2021 |
         |----------------|
      6. |  3   04sep2021 |
      7. |  3   04sep2021 |
         |----------------|
      8. |  4   01sep2020 |
      9. |  4   01sep2020 |
     10. |  4   01sep2020 |
         |----------------|
     11. |  5   05sep2021 |
     12. |  5   05sep2021 |
         |----------------|
     13. |  6   03sep2022 |
     14. |  6   03sep2022 |
     15. |  6   03sep2022 |
         |----------------|
     16. |  7           . |
     17. |  7           . |
         +----------------+
    Otherwise, on your technique in #1, consider:

    Code:
    bysort  id (date): replace date=date[_n-1] if missing(date) & !missing(date[_n-1])
    Last edited by Andrew Musau; 13 Sep 2024, 08:27.

    Comment


    • #3
      The code works. Thanks

      Comment

      Working...
      X