Creating dummy if values of variable are missing in all time periods

[Maxence Morlet]

Hi all,

Apologies if this topic has already been addressed, I've searched for answers online but have found none.

My data are panel, and dates span monthly from 2018m10 to 2021m9. A snippet:

input str20 SecId float month double PortfESGRiskScore str13 ESGRating
"F000000EU6" 705     . "Average"      
"F000000EU6" 706     . "Average"      
"F000000EU6" 707     . "Below Average"

Some funds have a nonmissing ESG rating for the entire sample, Some funds have a nonmissing ESG rating for certain periods and missings in ESG ratings for others (arbitrary), and certain funds have only missing values for ESG rating whatever the period.

1. I would like to code a binary variable equal to one for funds that are never rated, i.e. have only missings in ESG rating.

I have tried

g nonratedESG=0
bysort SecId (month): replace nonratedESG=1 if ESGRating=="" | ESGRating=="."

but this variable takes the value of one whenever there is a missing in ESG rating, and not only for funds that never have a nonmissing value in ESGRating.


Any suggestions?


2. I would also like to code a second distinct variable, which takes the value of 1 when a fund first receives its ESG rating i.e. goes from not being rated in the previous month (missing value of ESGRating) to having a rating in the current month.

I tried

g instant_impact_ESG=0
bysort SecId1 (month): replace instant_impact_ESG=1 if nonratedESG!=L.nonratedESG & ESGRating[_n-1]=="."

but this variable took the value of one for each first observation of each fund . Any suggestions on 2.?


Many thanks in advance!
Maxence

[Clyde Schechter]

by SecId, sort: egen nonratedESG = min(missing(PortfESGRiskScore))

by SecId (month), sort: gen n_ratings = sum(!missing(PortfESGRiskScore))
by SecId (month): gen byte instant_impact_ESG = (n_ratings == 1 & n_ratings[_n-1] != 1)

[Maxence Morlet]

Worked a wonder, thank you very much!

[Nick Cox]

Although we should be happy if you're happy, note that #1 implies that for you string period "." counts as missing just as empty string "" does. That is not what the missing() function ever checks. Missing with strings only ever means a completely empty string.

replace whatever = "" if inlist(trim(whatever), "", ".")

will trim leading and trailing spaces and map periods to empty.