Markov Modelling for Disease Progression Help

#1
Hey guys, I'm using a longitudinal data set to build a multi-state markov model in order to eventually calculate life expectancy for patients suffering from some disease, but im having some trouble and any help would be appreciated! I'm new to longitudinal data so go easy :p

The disease has 5 states of severity and then a 6th state is considered in the model to represent death.
A sample of my data showing the results of 2 patients is below:
(I tried to have it aligned but it wouldnt, sorry! Its really only the first and last columns that are important)

id datesample Gender DateofDeath ageattest state
1 15/11/2001 F . 97 3
1 14/10/2002 F . 98 3
2 06/07/2001 F 07/12/2001 87 5
2 09/07/2001 F 07/12/2001 87 4
2 11/07/2001 F 07/12/2001 87 4
2 12/07/2001 F 07/12/2001 87 3
2 13/07/2001 F 07/12/2001 87 3
2 16/07/2001 F 07/12/2001 87 3
2 08/08/2001 F 07/12/2001 87 3

So for patient 1 there are two observations showing the patient was in disease state 3 both times. The patient doesnt die (as there is no recorded death time) The patients progression in the model is therefore 3->3

For patient 2 there are seven observations showing the patient starts in disease state 5 but eventually moves into state 4 and then into state 3. There is also a noted date of death meaning that after state 3 the patient moves into state 6 (representing the death). The patients progression through the states is therefore 5->4->4->3->3->3->3->6 however this progression into state 6 isn't denoted properly in the data set so whenever i try to do any analyzing techniques (like using the msm package in R) the fact that patient 2 dies isnt represented on my transition matrix.

Ideally i would like to create a new row in the data set for every patient where a death time is denoted, ie

id datesample Gender DateofDeath ageattest state
1 15/11/2001 F . 97 3
1 14/10/2002 F . 98 3
2 06/07/2001 F 07/12/2001 87 5
2 09/07/2001 F 07/12/2001 87 4
2 11/07/2001 F 07/12/2001 87 4
2 12/07/2001 F 07/12/2001 87 3
2 13/07/2001 F 07/12/2001 87 3
2 16/07/2001 F 07/12/2001 87 3
2 08/08/2001 F 07/12/2001 87 3
2 07/12/2001F 07/12/2001 87 6

However I've no idea how to do this? I'm comfortable working with SAS, R or excel so if anyone has any ideas i would greatly appreciate it.