Create variable for "if any"

[Katie Holzer]

Hello, I have a dataset with multiple medications listed per study_id. Please see example below. I would like to create a variable "antidepress" that indicates whether any of the med variables (med1-med7) contain an antidepressant. For example, antidepress would =1 if med1, med2, med3, or med4, etc. = citalopram, or sertraline, etc.

input int study_id str34 med1 str33 med2 str28(med3 med4) str16 med5 str15(med6 med7)
  4 "cyclobenzaprine"  ""                       ""                       ""             "" "" ""
  5 "lorazepam"        "promethazine"           "trazodone"              ""             "" "" ""
  6 "alprazolam"       "eszopiclone"            ""                       ""             "" "" ""
  9 "cyclobenzaprine"  "temazepam"              ""                       ""             "" "" ""
  12 "citalopram"       ""                       ""                       ""             "" "" ""
 16 "cyclobenzaprine"  ""                       ""                       ""             "" "" ""
 23 "amitriptyline"    ""                       ""                       ""             "" "" ""
 28 "venlafaxine"      ""                       ""                       ""             "" "" ""
 31 "zzzquil"          ""                       ""                       ""             "" "" ""
 32 "trazodone"        "sertraline"             ""                       ""             "" "" ""

Thank you for your help!
Katie

[William Lisowski]

This isn't the answer you are expecting.

In your previous post at

https://www.statalist.org/forums/for...order-variable

I refrained from giving you the generic advice advising against reshaping your data into a wide layout. I should have done so.

The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data. You should try to achieve what you need with the data organized as it currently is, and seek the help of Statalist in doing so. The sort of problems you will encounter trying to use your reshaped data will almost certainly be solved by reshaping the data.

So in particular, if we start with the example data organized in a long layout that you provided in the earlier post (changing the fourth observation to make a better example), we have the code

* Example generated by -dataex-. To install: ssc install dataex
clear
input int study_id str34 med
  4 "cyclobenzaprine"       
  5 "lorazepam"             
  5 "promethazine"          
  5 "sertraline"             
  6 "alprazolam"            
  6 "eszopiclone"           
  7 "cyclobenzaprine"       
  7 "temazepam"             
  8 "citalopram"            
 9 "cyclobenzaprine"       
 22 "amitriptyline"         
 24 "venlafaxine"
end
generate antid = inlist(med,"citalopram","sertraline")
sort study_id, stable
by study_id: egen has_antid = max(antid)
list, sepby(study_id) abbreviate(12)

. list, sepby(study_id) abbreviate(12)

     +------------------------------------------------+
     | study_id               med   antid   has_antid |
     |------------------------------------------------|
  1. |        4   cyclobenzaprine       0           0 |
     |------------------------------------------------|
  2. |        5         lorazepam       0           1 |
  3. |        5      promethazine       0           1 |
  4. |        5        sertraline       1           1 |
     |------------------------------------------------|
  5. |        6        alprazolam       0           0 |
  6. |        6       eszopiclone       0           0 |
     |------------------------------------------------|
  7. |        7   cyclobenzaprine       0           0 |
  8. |        7         temazepam       0           0 |
     |------------------------------------------------|
  9. |        8        citalopram       1           1 |
     |------------------------------------------------|
 10. |        9   cyclobenzaprine       0           0 |
     |------------------------------------------------|
 11. |       22     amitriptyline       0           0 |
     |------------------------------------------------|
 12. |       24       venlafaxine       0           0 |
     +------------------------------------------------+

And if we insist on a wide layout, we can do that now.

drop antid
by study_id: generate mednum = _n
reshape wide med, i(study_id) j(mednum)
list, clean abbreviate(12)

. list, clean abbreviate(12)

       study_id              med1           med2         med3   has_antid  
  1.          4   cyclobenzaprine                                       0  
  2.          5         lorazepam   promethazine   sertraline           1  
  3.          6        alprazolam    eszopiclone                        0  
  4.          7   cyclobenzaprine      temazepam                        0  
  5.          8        citalopram                                       1  
  6.          9   cyclobenzaprine                                       0  
  7.         22     amitriptyline                                       0  
  8.         24       venlafaxine                                       0

[Carlo Lazzaro]

Katie:
an example with two active agents only is reported below:

. g d=.
(10 missing values generated)

. g wanted=.
(10 missing values generated)

. foreach var of varlist med1 - med7 {
  2. replace d=1 if `var'=="lorazepam" | `var'=="sertraline"
  3. replace wanted=d
  4.   }
(1 real change made)
(1 real change made)
(1 real change made)
(1 real change made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)
(0 real changes made)

. drop d

. list

     +--------------------------------------------------------------------------------------------+
     | study_id              med1           med2        med3   med4   med5   med6   med7   wanted |
     |--------------------------------------------------------------------------------------------|
  1. |        4   cyclobenzaprine                                                               . |
  2. |        5         lorazepam   promethazine   trazodone                                    1 |
  3. |        6        alprazolam    eszopiclone                                                . |
  4. |        9   cyclobenzaprine      temazepam                                                . |
  5. |       12        citalopram                                                               . |
     |--------------------------------------------------------------------------------------------|
  6. |       16   cyclobenzaprine                                                               . |
  7. |       23     amitriptyline                                                               . |
  8. |       28       venlafaxine                                                               . |
  9. |       31           zzzquil                                                               . |
 10. |       32         trazodone     sertraline                                                1 |
     +--------------------------------------------------------------------------------------------+

.

PS: I'm probably late to the party that William started !

[Emil Alnor]

As many other members of this multi-disciplinary forum I am familiar enough with the medications in your dataexample to tell which are anti-depressants and which are not, but assuming that all they all are anti-depressants this should generate the desired variable:

gen antidepressant_cnt = 0
foreach v of var med*{
replace antidepressant =antidepressant_cnt + (`v'!="")
}
recode antidepressant_cnt (1/99=1), gen(antidepressant)

[Katie Holzer]

William Lisowski Do you have a suggestion for "expression too long" with inlist?

[Hemanshu Kumar]

This happens when you give too many values to -inlist-. Typically this happens with strings, because inlist only permits upto 9 strings. Try multiple inlist (each with no more than 9 string values) with or operators?

... inlist(...) | inlist(...) | inlist(...)

[William Lisowski]

Apparently you have more than 9 antidepressant names to search for: inlist is limited to 10 string arguments or 250 numeric arguments. (The reason for this difference is apparently lost in the mists of time; as is the reason the chosen error message is so uninformative.)

The following shows how you can search for matches in groups of up to 9.

generate antid = inlist(med,"citalopram","sertraline")
replace antid = inlist(med,"another","yet another") if antid!=1
by study_id: egen has_antid = max(antid)

With this said, if the antidepressants are the first of several categories of drug you wish to search for, an even better approach is to build a 2-variable reference dataset of drug names and their category, and then merge this dataset to the dataset in the long layout.

Added in edit: My first code was incorrect on the replace command. And this crossed with post #6, which made me realize my error.

[Katie Holzer]

Thank you all very much!