Hi everybody
I have a questions that I hope that one you can solve. I have tried various manuals online but none of them seem to solve my sas problem.
I have more than 100.000 linies of patient admissions and sometimes the patients move from one department to another giving me seemingly a new admission however I would like to know when their journey started and when they finally left incl. the journey number. I have the data(mock) in the first 3 columns and would like the variables starting with X. The number of linies that can constitute one journey is in theory infinite.
Obs | Patient_RK | Start_Date | end_Date | Xstart | Xend | Xnumber |
1 | 19 | 18/12/2012 | 02/01/2013 | 18/12/2012 | 02/01/2013 | 1 |
2 | 19 | 02/01/2013 | 08/04/2013 | 18/12/2012 | 08/04/2013 | 1 |
3 | 43 | 12/05/2014 | 06/06/2014 | 12/05/2014 | 06/06/2014 | 1 |
42 | 172 | 05/10/2010 | 11/10/2010 | 05/10/2010 | 11/10/2010 | 1 |
43 | 172 | 11/10/2010 | 21/10/2010 | 05/10/2010 | 11/10/2010 | 1 |
44 | 172 | 07/01/2011 | 20/01/2011 | 07/01/2011 | 20/01/2011 | 2 |
48 | 172 | 10/07/2011 | 10/07/2011 | 10/07/2011 | 10/07/2011 | 6 |
49 | 172 | 19/07/2011 | 20/07/2011 | 19/07/2011 | 20/07/2011 | 7 |
50 | 172 | 20/07/2011 | 27/07/2011 | 19/07/2011 | 20/07/2011 | 7 |
51 | 172 | 28/08/2011 | 31/08/2011 | 28/08/2011 | 31/08/2011 | 8 |
53 | 172 | 06/10/2011 | 08/10/2011 | 06/10/2011 | 08/10/2011 | 10 |
54 | 172 | 08/03/2012 | 09/03/2012 | 08/03/2012 | 09/03/2012 | 11 |
55 | 172 | 09/03/2012 | 14/03/2012 | 08/03/2012 | 09/03/2012 | 11 |
56 | 172 | 14/03/2012 | 23/08/2012 | 08/08/2012 | 23/08/2012 | 11 |
I have this programming however it only works for two linies ad a time. Is it possible to get it to work for all of them.
data x; set x;
retain xstart xend xnumber;
format xstart ddmmyy10. xend ddmmyy10.;
by patient_RK;
if first.patient_RK then do;
xstart=start_date;
xend=end_date;
xnumber=1;
end;
if xend <start_date then do;
xnumber+1;
xstart=start_date;
xend=end_date;
end;
run;
Thanks in advance.
Solvej
You should post your data as a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Show the start data and the desired result. I think what you show is a final data set with extra varaibles that aren't needed.
Paste data into a text box using the {i} icon. I don't know what you pasted that data from but I can't paste into an editor in a form I'm
willing to spend the time to write a data step to read.
Is your Xnumber for Patiend 173 supposed to go from 2 to 6? If so what is the rule?
Something along these lines may get you started if I understand that the overlapping visit dats are counted as one visit and the count should be sequential.
proc sort data=have; by Patient_RK start_date End_date; run; data want; set have; by Patient_rk; retain xstart xend xnumber; format xstart xend ddmmyy10.; if first.Patient_rk then do; xstart=start_date; xend = end_date; xnumber=1; end; else do; if start_date <= xend then do; /* continuation of previous visit*/ xend = end_date; end; else do; /* new visit*/ xnumber+1; xstart=start_date; xend = end_date; end; end; run;
I don't follow your example. It doesn't start at 1 for each patient and why does it all of the sudden jump from2 to 6?
Art, CEO, AnalystFinder.com
You should post your data as a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Show the start data and the desired result. I think what you show is a final data set with extra varaibles that aren't needed.
Paste data into a text box using the {i} icon. I don't know what you pasted that data from but I can't paste into an editor in a form I'm
willing to spend the time to write a data step to read.
Is your Xnumber for Patiend 173 supposed to go from 2 to 6? If so what is the rule?
Something along these lines may get you started if I understand that the overlapping visit dats are counted as one visit and the count should be sequential.
proc sort data=have; by Patient_RK start_date End_date; run; data want; set have; by Patient_rk; retain xstart xend xnumber; format xstart xend ddmmyy10.; if first.Patient_rk then do; xstart=start_date; xend = end_date; xnumber=1; end; else do; if start_date <= xend then do; /* continuation of previous visit*/ xend = end_date; end; else do; /* new visit*/ xnumber+1; xstart=start_date; xend = end_date; end; end; run;
Thank you Ballardw
This is my first question in the forum. I very happy about the quick replies. Next time I will check out the instructions more carefully 🙂
I think that the code you made worked perfectly. I will have to check in more detail tomorrow.
Solvej
I haven't tested @ballardw's code, so I don't know if it does what you want. If it does, just ignore the following:
data want (drop=_:); set x (rename=(xstart=_xstart xend=_xend xnumber=_xnumber)); retain _last_start_date _last_end_date; format xstart ddmmyy10. xend ddmmyy10.; by patient_number; if first.patient_number then do; xstart=start_date; xend=end_date; xnumber=1; end; else if start_date<=(_last_end_date+1) then do; xstart=_last_start_date; xend=end_date; end; else do; xstart=start_date; xend=end_date; xnumber+1; end; output; _last_end_date=xend; _last_start_date=xstart; run;
Art, CEO, AnalystFinder.com
FWIW: @ballardw's code does the same as what my code does, but with one exception. In fact, his code matches what you said you wanted. I changed one of your requirements and suggest that you may want to change it as well.
If a patient ends a stay on 12mar2012, but whose next record starts on 13mar2012, I would think that you would want to consider that a continual stay. Anyhow, that is the only difference between our outputs.
Art, CEO, AnalystFinder.com
If you are talking about a record like obs 55, below, and you just want to delete such records, then the following (I think) does what you want:
data x (drop=x:); informat start_date end_date xstart xend ddmmyy10.; format start_date end_date xstart xend ddmmyy10.; input Obs Patient_number Start_Date end_Date Xstart Xend Xnumber; cards; 1 19 18/12/2012 02/01/2013 18/12/2012 02/01/2013 1 2 19 02/01/2013 08/04/2013 18/12/2012 08/04/2013 1 3 43 12/05/2014 06/06/2014 12/05/2014 06/06/2014 1 4 172 05/10/2010 11/10/2010 05/10/2010 11/10/2010 1 5 172 11/10/2010 21/10/2010 05/10/2010 11/10/2010 1 55 172 21/10/2010 21/10/2010 05/10/2010 11/10/2010 0 6 172 07/01/2011 20/01/2011 07/01/2011 20/01/2011 2 7 172 28/08/2011 31/08/2011 28/08/2011 31/08/2011 3 8 172 06/10/2011 08/10/2011 06/10/2011 08/10/2011 4 9 172 08/03/2012 09/03/2012 08/03/2012 09/03/2012 5 10 172 09/03/2012 14/03/2012 08/03/2012 09/03/2012 5 11 172 14/03/2012 23/08/2012 08/03/2012 23/08/2012 5 ; data want; set x; by Patient_number; retain xstart xend xnumber; format xstart xend ddmmyy10.; if first.Patient_number then do; xstart=start_date; xend = end_date; xnumber=1; end; else do; if start_date <= xend and end_date <= xend then delete; else if start_date <= xend then do; /* continuation of previous visit*/ xend = end_date; end; else do; /* new visit*/ xnumber+1; xstart=start_date; xend = end_date; end; end; run;
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.