Collapse command when identifying variables are missing

[Erik Anderson]

I have a large dataset that lists patient diagnosis in a single row with a patient name. Additional diagnosis are listed in new rows, but the names and other variables are not duplicated.

I'm attaching a sample dataset of what I have, and what I want it to look like. In the below example, patient Joe owns all the diagnosis in his row, and the diagnosis listed below that until Phil's row.

Any advice on how to make this work?


What I have:

patient	age	gender	diagnosis
Joe	39	m	diabetes
			htn
			oud
			aud
Phil	43	f	fx
			pna
			cellulitis
			sepsis
			pain
			diabetes
Jen	40	f	htn
			fx1
Valerie	44	m	fx2
			lac
			contusion
			aud
			stim

What I'm trying to get:

patient	age	gender	diagnosis
Joe	39	m	diabetes htn oud aud
Phil	43	f	fx pna cellulitis sepsis pain diabetes
Jen	40	f	htn fx1
Valerie	44	m	fx2 lac contusion aud stim

[Nick Cox]

Here is one way to do it. Note that in addition to dataex (see FAQ Advice #12) I used some surgery to get what I guess are your variables.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 patient byte age str6 gender str10 diagnosis
"Joe"     39 "m" "diabetes"  
""         . ""  "htn"      
""         . ""  "oud"      
""         . ""  "aud"      
"Phil"    43 "f" "fx"        
""         . ""  "pna"      
""         . ""  "cellulitis"
""         . ""  "sepsis"    
""         . ""  "pain"      
""         . ""  "diabetes"  
"Jen"     40 "f" "htn"      
""         . ""  "fx1"      
"Valerie" 44 "m" "fx2"      
""         . ""  "lac"      
""         . ""  "contusion"
""         . ""  "aud"      
""         . ""  "stim"      
end

gen work = diagnosis if patient != ""
replace work = work[_n-1] + " " + diagnosis if missing(work)
replace diagnosis = work

replace patient = patient[_n-1] if missing(patient)

collapse (mean) age (firstnm) gender (lastnm) diagnosis, by(patient)

list

     +-----------------------------------------------------------------+
     | patient   age   gender                                diagnosis |
     |-----------------------------------------------------------------|
  1. |     Jen    40        f                                  htn fx1 |
  2. |     Joe    39        m                     diabetes htn oud aud |
  3. |    Phil    43        f   fx pna cellulitis sepsis pain diabetes |
  4. | Valerie    44        m               fx2 lac contusion aud stim |
     +-----------------------------------------------------------------+

See also https://journals.sagepub.com/doi/pdf...36867X20909698

SJ-20-1 pr0071 . . . . Speaking Stata: Concatenating values over observations
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q1/20 SJ 20(1):236--243 (no commands)
discusses basic techniques for concatenating values of
variables over observations, emphasizing simple loops
that can be tuned to suit variants as desired

[Chen Samulsion]

Hi Anderson, you can read this thread, I think it will be helpful: https://www.statalist.org/forums/for...able-in-blocks
As to you question in #1, I try to give a solution below. The first part is you data, and the second part is code to generate what you wanted.

* Example generated by -dataex-. To install: ssc install dataex
clear
input strL patient float age str2 gender strL diagnosis
"Joe"     39 "m" "diabetes"  
"."        . "." "htn"      
"."        . "." "oud"      
"."        . "." "aud"      
"Phil"    43 "f" "fx"        
"."        . "." "pna"      
"."        . "." "cellulitis"
"."        . "." "sepsis"    
"."        . "." "pain"      
"."        . "." "diabetes"  
"Jen"     40 "f" "htn"      
"."        . "." "fx1"      
"Valerie" 44 "m" "fx2"      
"."        . "." "lac"      
"."        . "." "contusion"
"."        . "." "aud"      
"."        . "." "stim"      
end

gen id=_n
order id
replace patient=patient[_n-1] if patient[_n]=="."
bysort patient (id): gen id_inner=_n
move id_inner patient
gen wanted=""
bysort patient (id_inner): replace wanted=diagnosis+" "+wanted[_n-1]
sort id
list, sepby(patient)
bysort patient (id_inner): replace wanted=wanted[_N]
list, sepby(patient)

     +-----------------------------------------------------------------------------------------------+
     | id   id_inner   patient   age   gender    diagnosis                                    wanted |
     |-----------------------------------------------------------------------------------------------|
  1. |  1          1       Joe    39        m     diabetes                     aud oud htn diabetes  |
  2. |  2          2       Joe     .        .          htn                     aud oud htn diabetes  |
  3. |  3          3       Joe     .        .          oud                     aud oud htn diabetes  |
  4. |  4          4       Joe     .        .          aud                     aud oud htn diabetes  |
     |-----------------------------------------------------------------------------------------------|
  5. |  5          1      Phil    43        f           fx   diabetes pain sepsis cellulitis pna fx  |
  6. |  6          2      Phil     .        .          pna   diabetes pain sepsis cellulitis pna fx  |
  7. |  7          3      Phil     .        .   cellulitis   diabetes pain sepsis cellulitis pna fx  |
  8. |  8          4      Phil     .        .       sepsis   diabetes pain sepsis cellulitis pna fx  |
  9. |  9          5      Phil     .        .         pain   diabetes pain sepsis cellulitis pna fx  |
 10. | 10          6      Phil     .        .     diabetes   diabetes pain sepsis cellulitis pna fx  |
     |-----------------------------------------------------------------------------------------------|
 11. | 11          1       Jen    40        f          htn                                  fx1 htn  |
 12. | 12          2       Jen     .        .          fx1                                  fx1 htn  |
     |-----------------------------------------------------------------------------------------------|
 13. | 13          1   Valerie    44        m          fx2               stim aud contusion lac fx2  |
 14. | 14          2   Valerie     .        .          lac               stim aud contusion lac fx2  |
 15. | 15          3   Valerie     .        .    contusion               stim aud contusion lac fx2  |
 16. | 16          4   Valerie     .        .          aud               stim aud contusion lac fx2  |
 17. | 17          5   Valerie     .        .         stim               stim aud contusion lac fx2  |
     +-----------------------------------------------------------------------------------------------+

[Erik Anderson]

Thank you! This worked perfectly, I used Nick's example.