combining dates in 2 different columns

[Sateesh Shankaranarayana]

Dear Stata forum members,

I need advise on how to combine dates in 2 different columns in to one column and sort them.

I have a large data set of 40,000+ subjects. Each of them have about 6 to 10 dates of service (in one column) and date of first prescription (in another column) [similar to m:1, but same dataset]. I need to combine these dates to one column, so that I can sort them.

I very much appreciate your help.

With warm regards,
Sateesh

[George Ford]

help dataex

[Sateesh Shankaranarayana]

Code:
input id str11 datedx str11 first_prescription

id datedx first_pre~n
1. 1 "01 Jan 2020" "01 Apr 2020"
2. 1 "01 Feb 2020" "01 Apr 2020"
3. 1 "01 May 2020" "01 Apr 2020"
4. 1 "01 Aug 2020" "01 Apr 2020"
5. 2 "01 Jan 2021" "10 Apr 2020"
6. 2 "10 Feb 2021" "10 Apr 2020"
7. 2 "14 May 2021" "01 Apr 2020"
8. end

This is the sample of my data.
I need to combine datedx and first_prescription into one column so that I can sort the dates.

I very much appreciate help/suggestions.

Regards,
Sateesh

[George Ford]

Does this not work?

sort datedx first_prescription

[Sateesh Shankaranarayana]

No. I need to combine the 2 columns. It is like m:1.

If I can make it to one column, then it is easy

Regards
Sateesh

[George Ford]

Not clear to me how it would sort something like that. What determines order?

[George Ford]

as strings, you can combine easily:

g dateboth = datadx +  "_" +  first_prescription

I have no idea what a sort of that variable would do.

[Sateesh Shankaranarayana]

Dates in ascending order like for id 1: 1/1/2020, 1/2/2020, 1/1/2020, 1/5/2020 and 1/8/2020.

[Rich Goldstein]

if I understand you correctly, you first want to take your string dates and put them into Stata internal format dates; see

help datetime

and then you want to -reshape- your data from wide to long; see

help reshape

note that if you had supplied a -dataex- example (please read the FAQ), I might have shown actual code

[Sateesh Shankaranarayana]

Many thanks

[George Ford]

Here's a sketch. I randomly sorted the data since what you provided by dataex is already sorted.

clear
input id str11 datedx str11 first_prescription
id datedx first_pre~n
1 "01 Jan 2020" "01 Apr 2020"
1 "01 Feb 2020" "01 Apr 2020"
1 "01 May 2020" "01 Apr 2020"
1 "01 Aug 2020" "01 Apr 2020"
2 "01 Jan 2021" "10 Apr 2020"
2 "10 Feb 2021" "10 Apr 2020"
2 "14 May 2021" "01 Apr 2020"
end
g ran = runiform()
sort ran
g date1 = date(datedx,"DMY")
g date2 = date(first_prescription,"DMY")

format date1 %d 
format date2 %d 

sort date2 date1

[Sateesh Shankaranarayana]

Dear George, many thanks.