Putting variables into sequential order (by dates) per ID

[Yvonne Schmiedel]

Hi,

I am using STATA version 13.

I have a dataset with several date variables (start and stop dates of different treatements), which should have all been given sequentially.

I am trying to find out the pattern of treatments - in order words the order in which the treatments were given first, second, third according to the start date.

My plan was/ is:
1) I reshaped all the date variables (integer, float) from long to wide
2) Create a new variable, which puts the treatments according to the start date in order for each ID

With the latter I struggle.

Does anybody have any suggestions?

Many thanks,

Yvonne

[Nick Cox]

Please use dataex which you should download with

ssc install dataex

to give an example of your data.

#6, #12 and #18 of https://www.statalist.org/forums/help all apply here.

Most crucially, it seems that you need a long layout, not a wide one at all.

[Yvonne Schmiedel]

Thank you.

Unfortunately, I cannot download "dataex" - it takes certain adminitrative "rights" (on my work laptop), which I dont have.

see output:

ssc install dataex

server refused to send file
http://fmwww.bc.edu/repec/bocode/d/ either
1) is not a valid URL, or
2) could not be contacted, or
3) is not a Stata download site (has no stata.toc file).
r(672);

Is there any other way to get a solution?

These are my reatment start variables:
aftx_fuiv_start aftx_fupo_start ///
aftx_voiv_start aftx_vopo_start ///
aftx_abiv_start aftx_laiv_start ///
aftx_itrapo_start aftx_caspiv_start

There is 8 start dates for different treatments. The amount and order of these treatments would vary from ID to ID (icode).
Ideally I would like to get an output with the treatment order by start date for each patient.

Many thanks,

regards,

Yvonne

[Nick Cox]

You should still be able to copy and paste example data into Statalist, such as the results of

describe ID *iv_start
list ID *iv_start in 1/10

[Martin Mueller]

Dear Yvonne,

just in advance a small reminder that Statalist prefers to use full real names for their users.
As mentioned by Nick Cox, it is difficult without a data example. However, maybe the following line helps you solving your problem.

sort ID start_antibiotics
by ID: gen  no_of_treatment = _n

// List the distribution of the first treatment
tab treatment if no_of_treatment == 1

[Yvonne Schmiedel]

Dear Nick and Martin,

thanks for your reply. My contact states my full name - the displayed name seems non changeable.

Sorting the data seems simple. I realize that a new variable needs to be generated. But the difficulty is: each variable contains a treatment date.
My aim would be to have squential treatments for each individual /ID (icode)

i.e. ID1 aftx_fupo_start aftx_caspiv_start aftx_vopo_start showing that the patient with ID1 has taken these 3 treatments one after the other. while Pat. with ID2 might have only had one treatment. So, the sorting should be somehow by date.

sort icode aftx_fuiv_start aftx_fupo_start aftx_voiv_start aftx_vopo_start aftx_abiv_start aftx_laiv_start aftx_itrapo_start aftx_caspiv_start

gen treatment_seq = _n ???

[William Lisowski]

Perhaps something like

reshape long atfx_@_start, i(icode) j(treat) string
sort icode atfx__start

would transform your data into a long layout, as Nick suggests in post #2. It's hard to know without at least the sample that Nick requests in post #4 so we can test the code.

To change your display name, click "CONTACT US" in the right corner at the bottom of every Statalist page and send a message to the administrators to request the change. You are correct, it is not something you can do yourself.

Added in edit: It's possible that reshaping isn't needed if you start from the original data before you reshaped it from long to wide, which was probably not the right thing to do. 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.

[Yvonne Schmiedel]

Hi, thanks for your replies! Sorry for some silence.

ad William: I managed to get the username changed using the "contact us" service. Thank you.

Regarding my enquiry:

It was / is correct to convert data into long format.

With help from Martin Müller, I managed to get the desired result.

There may be a simpler way, but here it is:


local i=1
foreach x of varlist aftx_fuiv_start aftx_fupo_start aftx_voiv_start aftx_vopo_start aftx_abiv_start aftx_laiv_start aftx_itrapo_start aftx_caspiv_start{
rename `x' treatment_start`i'
local i = `i' + 1
}

reshape long treatment_start treatment_stop, i(icode) j(treatment)
replace treatment_start=0 if antifungal==0
replace treatment = treatment_start if treatment_start==0
drop if treatment_start==.
sort icode treatment_start
by icode: gen no_of_treatment = _n
drop if antifungal==0 & no_of_treatment>1
br icode treatment_start treatment no_of_t

gen sequence = treatment if no_of_treatment==1
by icode: egen dummy = max(sequence)
replace sequence = dummy
drop dummy

replace sequence = sequence * 10
replace sequence = sequence + treatment if no_of_treatment==2
by icode: egen dummy = max(sequence)
replace sequence = dummy
drop dummy

replace sequence = sequence * 10
replace sequence = sequence + treatment if no_of_treatment==3
by icode: egen dummy = max(sequence)
replace sequence = dummy
drop dummy

replace sequence = sequence * 10
replace sequence = sequence + treatment if no_of_treatment==4
by icode: egen dummy = max(sequence)
replace sequence = dummy
drop dummy

tab seq if no_of==1, m

Thank you all!

[William Lisowski]

Thank you for closing the loop, and for taking care of your screen name.

With regard to there being a simpler way, simplicity is in the eye of the beholder. I tend to consider how simple it is to read code and understand what it does, not how few lines it takes to write. Sometimes the two go together, sometimes they do not.

With that said, you may find it useful in similar circumstances to know that the rename command has advanced syntax that makes it possible to rename groups of variables, without needing to use a loop. In your case, I think

rename (aftx_fuiv_start aftx_fupo_start aftx_voiv_start aftx_vopo_start aftx_abiv_start aftx_laiv_start aftx_itrapo_start aftx_caspiv_start) (treatment_start_#)

would accomplish what you want.

You can learn the details of this from the output of help rename group .

[Yvonne Schmiedel]

Thank you William!

I totally agree.

Thanks for your remark on the rename command.