Auto fill for the missing variables after reshape

[Cong Gu]

Dear Statalist,

Hope all is well.

I am doing the reshape (i.e., reshape long) work for the data. However, when all the process has been accomplished, I find that only the start year and the end year provides information for each contract (i.e, contract), while the information for other years within the period is missing. For example, for contract_id 2, the start year after reshaping is 1987 and the end year is 1994, and all the information for these two specific years is completed. However, the information for the contact 2 from 1988 to 1993 is missing. Ideally, all the information for these years should be the same as presented in either 1987 or 1994. Could you please let me know how to fill these information for each contract after the reshaping?

Many thanks in advance.

My data looks like:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(contract_id year) double(FacilityID PackageID BorrowerCompanyID CompanyID) long gvkey str76 Company double LenderID str76 Lender str26 LenderRole double BankAllocation str4(AgentCredit LeadArrangerCredit)
1 1987  5 101 3769 3769  9362 "Sage-Allen Co"                        6828 "Fleet Real Estate"             "Lead bank" 100 "No"  "Yes"
2 1987 11 105 3782 3782 12826 "Hanson Trust"                         5891 "Chemical Bank"                 "Agent"       . "Yes" "Yes"
2 1988  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1989  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1990  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1991  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1992  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1993  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
2 1994 11 105 3782 3782 12826 "Hanson Trust"                         5891 "Chemical Bank"                 "Agent"       . "Yes" "Yes"
3 1987 13 107    4    4  6300 "American Community Development Group" 6128 "Capital Bank NA"               "Lead bank" 100 "No"  "Yes"
3 1988 13 107    4    4  6300 "American Community Development Group" 6128 "Capital Bank NA"               "Lead bank" 100 "No"  "Yes"
4 1987 14 108    4    4  6300 "American Community Development Group" 5930 "First American Bank & Trust"   "Lead bank" 100 "No"  "Yes"
4 1988 14 108    4    4  6300 "American Community Development Group" 5930 "First American Bank & Trust"   "Lead bank" 100 "No"  "Yes"
5 1987 16 110 3788 3788  3321 "Computer Image"                       7837 "Banque Worms SA"               "Lead bank" 100 "No"  "Yes"
5 1988  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
5 1989 16 110 3788 3788  3321 "Computer Image"                       7837 "Banque Worms SA"               "Lead bank" 100 "No"  "Yes"
6 1987 17 111 3780 3780  6005 "Integrated Resources"                 6039 "National Westminster Bank USA" "Lead bank" 100 "No"  "Yes"
6 1988 17 111 3780 3780  6005 "Integrated Resources"                 6039 "National Westminster Bank USA" "Lead bank" 100 "No"  "Yes"
7 1987 18 111 3780 3780  6005 "Integrated Resources"                 6039 "National Westminster Bank USA" "Lead bank" 100 "No"  "Yes"
7 1988  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
7 1989  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
7 1990 18 111 3780 3780  6005 "Integrated Resources"                 6039 "National Westminster Bank USA" "Lead bank" 100 "No"  "Yes"
8 1987 19 112 3804 3804 12332 "American Cable Systems of California" 7827 "Toronto Dominion Bank"         "Agent"       . "Yes" "Yes"
8 1988  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1989  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1990  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1991  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1992  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1993  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1994  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1995  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1996  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
8 1997 19 112 3804 3804 12332 "American Cable Systems of California" 7827 "Toronto Dominion Bank"         "Agent"       . "Yes" "Yes"
9 1987 21 113 3799 3799 15242 "Black Hawk Holdings"                   570 "Pacificorp Credit"             "Lead bank" 100 "No"  "Yes"
9 1988  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
9 1989  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
9 1990  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
9 1991  .   .    .    .     . ""                                        . ""                              ""            . ""    ""   
9 1992 21 113 3799 3799 15242 "Black Hawk Holdings"                   570 "Pacificorp Credit"             "Lead bank" 100 "No"  "Yes"
end

[Carlo Lazzaro]

Cong:
the following example applies to numeric values only:

. foreach var of varlist FacilityID- gvkey  {
  2. bysort contract_id: replace  `var'=`var'[_N] if `var'==.
  3.   }

[Clyde Schechter]

On the assumptions (not verified) the information is always available in the first observation and that the information in the last observation for each contract_id is consistent with that of the first observation, and (verified) that there is never more than one observation for a given contract_id in the same year:

isid contract_id year, sort
ds contract_id year, not
foreach v of varlist `r(varlist)' {
    by contract_id (year): replace `v' = `v'[1] if missing(`v')
}

Added: Crossed with #2.

[Cong Gu]

Cong:
the following example applies to numeric values only:

. foreach var of varlist FacilityID- gvkey {
2. bysort contract_id: replace `var'=`var'[_N] if `var'==.
3. }

Dear Carlo,

Many thanks for the code. They are very helpful.

[Cong Gu]

On the assumptions (not verified) the information is always available in the first observation and that the information in the last observation for each contract_id is consistent with that of the first observation, and (verified) that there is never more than one observation for a given contract_id in the same year:

isid contract_id year, sort
ds contract_id year, not
foreach v of varlist `r(varlist)' {
by contract_id (year): replace `v' = `v'[1] if missing(`v')
}

Added: Crossed with #2.

Dear Clyde,

Many thanks for the kind help. Much appreciated.