Hi,
I Have repeated IDs of observations. I want to repeat the result of each cell value of a column in the other cells of that column if the ID is same.
For example:
replace var11 = 1 if drug_level4value == "heparin group"
*Then I want to repeat it for the repeated IDs even though the above "if" is not true for other observations of each ID.
I am not sure, from your description, what you want to do. If you want each patient record to include the information that they were prescribed a heparin group drug, one way to do it is to sort the records by id and date, and then draw the information you need from the previous record.
gen ever_heparin = drug_level4value == "heparin group"
bysort id (date):replace ever_heparin=1 if (drug_level4value[_n-1] == "heparin group") & (_n ~= 1)
We start by generating a variable that records whether the patient ever had heparin, based on drug_level4value. This creates a logical variable, coded zero or one.
(Note : because "by" is a common word, I'll distinguish Stata terms by writing them with dashes before and after, for example -by-).
Now we use the -bysort- prefix to run the next command on data, sorted by id and date. But notice that the date variable is in brackets. This means that the data will be sorted by id and within each id it will be sorted by date, but the -by- prefix will generate groups based only on unique values of id.
The -replace- command that follows replaces ever_heparin with a 1 (true) if the value of drug_level4value for the previous observation is "heparin group". This is done using one of Stata's invisible variables – _n. The variable _n marks the position of each observation in the dataset or, as in this case, within the group defined by the -by- prefix. So drug_level4value[_n-1] means "the value of drug_level4value for the previous observation. (drug_level4value[1] would point to the first observation and drug_level4value[_N] would point to the last observation in the data or the by-group. You might as well know that the other important invisible variable is _N, which is the total number of observations in the data or in the -by- group).
Finally, we don't want this to happen for the first observation, because _n-1 is zero, and Stata might get confused, so the last condition in the command checks that the observation is not the first observation in its by group.
Of course, you might want to do something else. In which case, I've simply amused myself with trying to explain uses of subscripting and -by-
Dear Prof. Ronán Michael Conroy
Thank you very much. Right now, I am trying to perform your comments.
You correctly understood my mean.
For better clarifying, e.g.
- I have 10 observation for an ID.
- Among them, 3 ones are "heparin group" positive (according to your command, ever_heparin = 1 in 3 ones and = 0 in the 7 others.
- I want to copy the value "1" in that 7 ones in spite of "heparin group" negativity.
*** I add that my IDs have fixed date! The repeated observations are due to different drug services.
It is a big dataset on Covid resulted from data mining. I want to define binary variables for drugs, and then remove the duplicate IDs.
OK. Then the trick is that you need to make sure that if your dates are all the same, then the problem shifts slightly.
What we need to do is to make sure that if ever_heparin == 1, we know exactly where that observation is within the group formed by the unique ID numbers. So we can make sure that it's the last observation by sorting like this:
gen ever_heparin = drug_level4value == "heparin group"
bysort id ever_heparin: replace ever_heparin == 1if ever_heparin[_N]==1
No matter how many of the records for the same ID are positive for ever_heparin, as long as there is at least one, the last one has to be 1.
The notation _N identifies the last observation in the -by- group.
Drug databases – nightmares! Usually full of variant spellings too. In my experience, Nu-Seals aspirin holds the record for number of misspellings (11 in one study I did, and 13 in a study in a neighbouring university).
Edit : feel free to send me the code – Covid research is time-critical.
Ronán Michael Conroy
Thank you again. The problem was solved as below as you have taught as above!
gen ever_heparin = drug_level4value == "heparin group"
bysort id (ever_heparin): replace ever_heparin = 1 if ever_heparin[_N]==1
For the problem of drug name spelling I use strpos().
It is my pleasure to receive help from you.
Kind regards!
I can pass on a couple of tips about very messy drug datasets.
1. Spring-clean the text variables by using
replace drug = lower(trim(drug))
this zaps leading and trailing spaces and converts all text to lower case
2. If you have a massive problem with misspellings, export the variable to a plain text file and open it in a text processor or word processor. spell-check it! Once you correct a variant spelling once, it gets corrected everywhere.
3. Your're right: -strpos- is the best friend you have here!
Thanks again
My dataset was successfully created with below code:
replace service = lower(trim(service))
gen sofosbuvir = strpos(service, "sofosbuvir")
bysort compositionuid (sofosbuvir): replace sofosbuvir = 1 if sofosbuvir[_N]==1
gen lopinavir = strpos(service, "lopinavir")
bysort compositionuid (lopinavir): replace lopinavir = 1 if lopinavir[_N]==1
gen ribavirin = strpos(service, "ribavirin")
bysort compositionuid (ribavirin): replace ribavirin = 1 if ribavirin[_N]==1
gen atazanavir = strpos(service, "atazanavir")
........[many others not shown].........
bysort compositionuid (octagam): replace octagam = 1 if octagam[_N]==1
gen heparin_group = strpos(drug_level4value, "heparin group")
bysort compositionuid (heparin_group): replace heparin_group = 1 if heparin_group[_N]==1
duplicates drop compositionuid, force
Seyyed Amir Yasin Ahmadi Delighted to hear that.
You will notice that your lines repeat the same syntax with the insertion of a new drug name at each repetition.
You should check out the -foreach- command.
foreach drug in sofosbuvir lopinavir ribavirin atazanavir {
gen `drug' = strpos(service, "`drug'")
bysort compositionuid (`drug'): replace `drug' = 1 if `drug'[_N]==1
}
I have defined a list called drug. The commands will repeat once for each element in the list.
When referring to the list element, note that I have to surround it with single quotes `' (the open-single-quote is the backtick mark, and the close-single-quote is the standard single quote)
When I say you should check out the -foreach- command, I mean the next time you have a task like this!
Thanks Ronán Michael Conroy
It was your kindness, not a duty! (this sentence is widely used in our language with a positive connotation)
I tried your foreach loop and it was fantastic. However, as you mentioned such datasets have a lot of limitations such as need to AND OR NOT.
I appreciate you (with meaning thank or acknowledge in English) if you introduce me some free sources for the basics of programming in R and Stata. Because programming in them has a different world in comparison to statistical analysis.
Best!