How to Create a New Variable to Indicate Yes/No/NA in Stata?

[smith Jason]

I have a dataset like this,
clear
input byte (id gr state)
1 1 0
1 2 0
1 2 1
1 3 0
1 4 0
1 4 1
1 4 1
1 4 1
1 5 0
1 6 0
2 1 0
2 2 0
2 3 0
2 4 0
2 4 1
2 5 .
2 5 1
2 6 0
3 1 0
3 . .
3 3 0
3 4 0
3 . .
3 6 0
4 1 0
4 1 1
4 2 0
4 3 0
4 4 0
4 5 0
4 6 0
end

I want to create a new variable called "GR", indicating if a student was ever retained in grades.
The rule to create "GR" is as follows,
Within id, as long as there is a 1 in the "State" variable, then GR==1
within id, when all values of state==0, then GR==0
Within id, as long as the cells of the "state" consist of "0" and "missing values", then GR==missing value.
Can someone help me with the Stata code to achieve this goal?
Thank you!

[Clyde Schechter]

assert inlist(state, 0, 1) | missing(state)
by id, sort: egen wanted = max(state)
by id: egen nm_state = count(state) // # OF NON-MISSING OBS OF STATE IN THIS ID
by id: replace wanted = . if wanted == 0 & nm_state < _N

[Ken Chui]

Calling one variable gr and one variable GR is legal in Stata but I don't think it's a good practice. I'm calling it gr2. I also added a case ID 5 with all 0 in state just to test the code.

clear
input byte (id gr state)
1 1 0
1 2 0
1 2 1
1 3 0
1 4 0
1 4 1
1 4 1
1 4 1
1 5 0
1 6 0
2 1 0
2 2 0
2 3 0
2 4 0
2 4 1
2 5 .
2 5 1
2 6 0
3 1 0
3 . .
3 3 0
3 4 0
3 . .
3 6 0
4 1 0
4 1 1
4 2 0
4 3 0
4 4 0
4 5 0
4 6 0
5 1 0
5 2 0
5 3 0
5 4 0
5 5 0
5 6 0
end

preserve
gen is_miss = state == .
collapse (min) min_state = state (max) max_state=state miss_state=is_miss, by(id)
gen     gr2 = 1 if  max_state == 1
replace gr2 = 0 if  max_state == 0 & min_state == 0 & miss_state == 0
replace gr2 = . if miss_state == 1 & max_state == 0
keep id gr2
save tempfile, replace
restore

merge m:1 id using tempfile
drop _merge
capture erase tempfile.dta

[smith Jason]

Thank you all!

[Nick Cox]

https://www.statalist.org/forums/help#before

3. What should I do before I post?

Before posting, search the forum for similar questions and consider other ways of finding information:

• the online help for Stata
• Stata's search command, which can tell you about all built-in Stata commands, all ado-files published in the Stata Journal, all FAQs on the Stata website, www.stata.com, and user-written Stata programs available on the Internet (if you have Stata 12 or earlier, you can use findit to search all these sources at once)

See also

https://www.stata.com/support/faqs/d...ummy-variables

https://www.stata.com/support/faqs/d...rue-and-false/

https://www.stata-journal.com/articl...article=dm0099

https://www.stata-journal.com/articl...article=dm0087