bysort and summary statistics

[Patrick Nadol]

Dear All-
I am trying to create a new variable ('reference_median') populated with the median value of the number of staff (var: 'fte') for each level of health care worker (var: 'cadre_cat'), type of support (var: 'support_type'), and clinic size category (var: cop18tier). I understand that this is some combination of a bysort command along the lines of: bysort cadre_cat support_type (cop18tier): egen ref_fte=median(fte).

The other wrinkle is that I want to populate all of the other quartiles (var: quartile_txcurr) with the median values that I get for quartile_txcurr=4 (quartile 4 is the best performing quartile and I want to use their median number of staff as a reference for the other quartiles).

I can't seem to get the right combination---I have attached a sub-set of my dataset here.


I would very much appreciate any quick help/guidance anyone may have.

Best-Patrick

[Nick Cox]

Please back up and read and act on https://www.statalist.org/forums/help#stata Spreadsheet attachments are specifically deprecated for the reasons given here.

Would you send a Stata ,dta file to an Excel forum?

Please supply a data example in the form requested.

[Patrick Nadol]

will this work?:

input str2 cadre str16 cadre_cat long patients_per str3 support_type byte(quartile_txnew quartile_txcurr) float fte str6 cop18tier
"do" "clinical" . "sag" 3 4 4 "TIER 1"
"cw" "lay" . "oth" 3 4 0 "TIER 2"
"cw" "lay" . "tot" 3 4 0 "TIER 3"
"do" "clinical" . "oth" 4 4 0 "TIER 2"
"dn" "clinical support" . "tot" 3 4 0 "TIER 2"
"se" "other" . "usg" 4 4 6 "TIER 4"
"dc" "management" 263 "sag" 1 4 1 "TIER 1"
"dc" "management" . "oth" 3 4 0 "TIER 1"
"tc" "lay" . "usg" 1 4 0 "TIER 1"
"sn" "clinical" . "oth" 3 4 6 "TIER 1"
"dn" "clinical support" 234000 "tot" 4 4 .01 "TIER 3"
"pn" "clinical" . "oth" 3 4 0 "TIER 1"
"do" "clinical" . "usg" 1 4 0 "TIER 1"
"na" "clinical" . "tot" 3 4 0 "TIER 1"
"hp" "lay" . "sag" 2 4 0 "TIER 3"
"ad" "lay" . "usg" 3 4 0 "TIER 1"
"cw" "lay" . "usg" 4 4 0 "TIER 2"
"lc" "lay" 1720 "sag" 3 4 1 "TIER 4"

[Nick Cox]

Thanks for the data example. Note also, as here, the use of CODE delimiters and -- for spreading results in a subset more widely -- https://www.stata-journal.com/sjpdf....iclenum=dm0055 sections 9 and 10.

clear 
input str2 cadre str16 cadre_cat long patients_per str3 support_type byte(quartile_txnew quartile_txcurr) float fte str6 cop18tier
"do" "clinical" . "sag" 3 4 4 "TIER 1"
"cw" "lay" . "oth" 3 4 0 "TIER 2"
"cw" "lay" . "tot" 3 4 0 "TIER 3"
"do" "clinical" . "oth" 4 4 0 "TIER 2"
"dn" "clinical support" . "tot" 3 4 0 "TIER 2"
"se" "other" . "usg" 4 4 6 "TIER 4"
"dc" "management" 263 "sag" 1 4 1 "TIER 1"
"dc" "management" . "oth" 3 4 0 "TIER 1"
"tc" "lay" . "usg" 1 4 0 "TIER 1"
"sn" "clinical" . "oth" 3 4 6 "TIER 1"
"dn" "clinical support" 234000 "tot" 4 4 .01 "TIER 3"
"pn" "clinical" . "oth" 3 4 0 "TIER 1"
"do" "clinical" . "usg" 1 4 0 "TIER 1"
"na" "clinical" . "tot" 3 4 0 "TIER 1"
"hp" "lay" . "sag" 2 4 0 "TIER 3"
"ad" "lay" . "usg" 3 4 0 "TIER 1"
"cw" "lay" . "usg" 4 4 0 "TIER 2"
"lc" "lay" 1720 "sag" 3 4 1 "TIER 4"
end 

bysort cadre_cat support_type cop18tier: egen ref_fte=median(cond(quartile_txcurr == 4, fte, .))

list cadre_cat support_type cop18tier quartile_txcurr fte ref_fte , sepby(cadre_cat support_type cop18tier) 

    +-------------------------------------------------------------------+
     |        cadre_cat   suppor~e   cop18t~r   quarti~r   fte   ref_fte |
     |-------------------------------------------------------------------|
  1. |         clinical        oth     TIER 1          4     0         3 |
  2. |         clinical        oth     TIER 1          4     6         3 |
     |-------------------------------------------------------------------|
  3. |         clinical        oth     TIER 2          4     0         0 |
     |-------------------------------------------------------------------|
  4. |         clinical        sag     TIER 1          4     4         4 |
     |-------------------------------------------------------------------|
  5. |         clinical        tot     TIER 1          4     0         0 |
     |-------------------------------------------------------------------|
  6. |         clinical        usg     TIER 1          4     0         0 |
     |-------------------------------------------------------------------|
  7. | clinical support        tot     TIER 2          4     0         0 |
     |-------------------------------------------------------------------|
  8. | clinical support        tot     TIER 3          4   .01       .01 |
     |-------------------------------------------------------------------|
  9. |              lay        oth     TIER 2          4     0         0 |
     |-------------------------------------------------------------------|
 10. |              lay        sag     TIER 3          4     0         0 |
     |-------------------------------------------------------------------|
 11. |              lay        sag     TIER 4          4     1         1 |
     |-------------------------------------------------------------------|
 12. |              lay        tot     TIER 3          4     0         0 |
     |-------------------------------------------------------------------|
 13. |              lay        usg     TIER 1          4     0         0 |
 14. |              lay        usg     TIER 1          4     0         0 |
     |-------------------------------------------------------------------|
 15. |              lay        usg     TIER 2          4     0         0 |
     |-------------------------------------------------------------------|
 16. |       management        oth     TIER 1          4     0         0 |
     |-------------------------------------------------------------------|
 17. |       management        sag     TIER 1          4     1         1 |
     |-------------------------------------------------------------------|
 18. |            other        usg     TIER 4          4     6         6 |
     +-------------------------------------------------------------------+