Generate new var based on an ID and a group

[Megan Forecki]

Hello!

I am trying to create a new variable "Complete" with the below data based on Ccid (can be considered a unique personid) and topic.

Essentially, I only want "Complete" to show as "Yes" (per the below example...or a 1 works too) if all of "value2" = "completed" in the same topic and same Ccid. Below is where I am starting:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 Ccid byte topic str9 value2
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 1 "completed"
"17255" 2 "to do"    
"17255" 2 "to do"    
"17255" 2 "to do"    
"17255" 2 "to do"    
"17255" 2 "to do"    
"17255" 2 "completed"
"17255" 2 "completed"
"17255" 3 "unlocked" 
"17255" 3 "to do"    
"17255" 3 "to do"    
"17255" 3 "completed"
"17255" 3 "completed"
"17255" 3 "completed"
"17255" 3 "completed"
"17255" 3 "to do"    
"17255" 3 "to do"    
"17255" 3 "to do"    
"17432" 1 "unlocked" 
"17432" 1 "unlocked" 
"17432" 1 "unlocked" 
"17432" 1 "unlocked" 
"17432" 1 "unlocked" 
"17432" 1 "unlocked" 
"17432" 1 "to do"    
"17432" 1 "unlocked" 
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 2 "to do"    
"17432" 3 "to do"    
"17432" 3 "to do"    
"17432" 3 "to do"    
"17432" 3 "to do"    
"17432" 3 "completed"
"17432" 3 "completed"
"17432" 3 "completed"
"17432" 3 "unlocked" 
"17432" 3 "completed"
"17432" 3 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 1 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 2 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
"16755" 3 "completed"
end

And below here how I'd like the data to look.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 Ccid byte topic str9 value2 str3 Complete
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 1 "completed" "Yes"
"17255" 2 "to do"     ""   
"17255" 2 "to do"     ""   
"17255" 2 "to do"     ""   
"17255" 2 "to do"     ""   
"17255" 2 "to do"     ""   
"17255" 2 "completed" ""   
"17255" 2 "completed" ""   
"17255" 3 "unlocked"  ""   
"17255" 3 "to do"     ""   
"17255" 3 "to do"     ""   
"17255" 3 "completed" ""   
"17255" 3 "completed" ""   
"17255" 3 "completed" ""   
"17255" 3 "completed" ""   
"17255" 3 "to do"     ""   
"17255" 3 "to do"     ""   
"17255" 3 "to do"     ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "unlocked"  ""   
"17432" 1 "to do"     ""   
"17432" 1 "unlocked"  ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 2 "to do"     ""   
"17432" 3 "to do"     ""   
"17432" 3 "to do"     ""   
"17432" 3 "to do"     ""   
"17432" 3 "to do"     ""   
"17432" 3 "completed" ""   
"17432" 3 "completed" ""   
"17432" 3 "completed" ""   
"17432" 3 "unlocked"  ""   
"17432" 3 "completed" ""   
"17432" 3 "completed" ""   
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 1 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 2 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
"16755" 3 "completed" "Yes"
end

You can see by Ccid that if topic = 1 and value2=completed, then Complete="Yes" but if any of the value2's with corresponding topic and Ccid is not equal to "completed" then the full topic was not completed.

I have tried egen commands with max and min, and I know I need to use a group indicator, but I have been having trouble for hours now trying to determine the correct command. I appreciate any insight folks can offer! Please feel free to direct me to another thread if easier. I have searched for my problem here/on the web, but I may not be specifying it correctly to generate the right search results either.

Thank you!

[Nick Cox]

Thanks for clear question and data example.

gen long obsno = _n 

bysort Ccid topic (value2) : gen wanted = "Yes" if value2[1] == "completed" & value2[_N] == "completed"

sort obsno 

list, sepby(Ccid)

See also https://www.stata.com/support/faqs/d...ions-in-group/ for the principle. If after sorting the first and last value are the same, then they all are.

[Megan Forecki]

Thank you!!!