Expand observations by group with respect to a Year variable

[Jack David]

Hello,

Looking for some advice on a basic issue. I have the data below on sovereign credit ratings. I am looking to expand/fill down the Year variable by each Sovereign, from the minimum Year for the Sovereign to the max Year of the sample which is 2022 (alternatively, fill/expand from minimum to maximum Year of the sample by Sovereign, whichever is more straightforward). Then, I would like to carry forward the last value in FCLT and Outlook into subsequent years.

Example - fill and expand the observations for Albania from 2010 to 2022, and then fill in the missing values of FCLT and Outlook, which would be "B+" and "Stable" for the newly-generated 2011 and 2012 observations, and so on.

clear
input str38 Sovereign str3(ISO Agency) int(Date Year) str4 FCLT str10 Outlook
"Abu Dhabi"  "ABU" "S&P" 17349 2007 "AA"   "Stable"   
"Albania"    "ALB" "S&P" 20489 2016 "B+"   "Stable"   
"Albania"    "ALB" "S&P" 20006 2014 "B"    "Positive" 
"Albania"    "ALB" "S&P" 19698 2013 "B"    "Negative" 
"Albania"    "ALB" "S&P" 18371 2010 "B+"   "Stable"   
"Andorra"    "AND" "S&P" 18043 2009 "A+"   "Stable"   
"Andorra"    "AND" "S&P" 16741 2005 "AA"   "Stable"   
"Andorra"    "AND" "S&P" 22029 2020 "BBB"  "Stable"   
"Andorra"    "AND" "S&P" 22841 2022 "BBB+" "Stable"   
"Andorra"    "AND" "S&P" 18427 2010 "A"    "Negative" 
"Andorra"    "AND" "S&P" 17756 2008 "AA-"  "Negative" 
"Andorra"    "AND" "S&P" 20664 2016 "BBB-" "Stable"   
"Andorra"    "AND" "S&P" 19330 2012 "A-"   "Negative" 
"Andorra"    "AND" "S&P" 19999 2014 "BBB+" "Stable"   
"Andorra"    "AND" "S&P" 16294 2004 "AA"   "Stable"   
"Andorra"    "AND" "S&P" 15831 2003 "AA-"  "Stable"   
"Andorra"    "AND" "S&P" 21749 2019 "BBB"  "Positive" 
"Andorra"    "AND" "S&P" 17394 2007 "AA"   "Negative" 
"Andorra"    "AND" "S&P" 21028 2017 "BBB"  "Stable"   
"Andorra"    "AND" "S&P" 20263 2015 "BBB-" "Negative" 
"Angola"     "AGO" "S&P" 20314 2015 "B+"   "Negative" 
end

Many thanks for your help.

[Clyde Schechter]

by Sovereign (Year), sort: gen int final_year = Year[_N]
frame put Sovereign ISO Agency if final_year != 2022, into(stubs)
frame stubs {
    duplicates drop
    gen Year = 2022
}
frameappend stubs

encode Sovereign, gen(sovereign)
xtset sovereign Year
tsfill
foreach v of varlist Sovereign ISO Agency FCLT Outlook {
    by sovereign (Year): replace `v' = `v'[_n-1] if missing(`v')
}
drop final_year

Note: The variable Sovereign isn't very helpful in this data set. As a string, it can't be used as a panel indicator. It is also redundant of ISO, and, more important, also of the newly created variable sovereign (N.B. lower case). You might want to drop Sovereign altogether and work with sovereign as the panel variable from this point on.

[Jack David]

Clyde, many thanks for your response. Unfortunately the -frame- command is unrecognised, perhaps because I am working in Stata 13. Is there an equivalent, or a workaround, for older versions of Stata? Thanks

[Clyde Schechter]

by Sovereign (Year), sort: gen int final_year = Year[_N]
preserve
tempfile stubs
keep if final_year != 2022
keep Sovereign ISO Agency
duplicates drop
gen Year = 2022
save `stubs'

restore
append using `stubs'
encode Sovereign, gen(sovereign)
xtset sovereign Year
tsfill
foreach v of varlist Sovereign ISO Agency FCLT Outlook {
    by sovereign (Year): replace `v' = `v'[_n-1] if missing(`v')
}
drop final_year

The Forum FAQ does advise that those not using the most recent version of Stata (currently 17) should state in their post what version they are using. This prevents both responders from wasting their time writing code the enquirer cannot run and the enquirers from wasting their time trying to do so. I would say that this is particularly true for very old versions like 13--much has changed since then.

[Jack David]

My apologies, you are right. Thanks for the advice.