BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Solvej
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

8 REPLIES 8
art297
Opal | Level 21

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

 

Solvej
Obsidian | Level 7
Hi Again

I apologize I made some mistakes when trying to make an example. I hope this is better.

Best wishes Solvej
Obs

Patient_number

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

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

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


ballardw
Super User

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;
Solvej
Obsidian | Level 7

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

art297
Opal | Level 21

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

art297
Opal | Level 21

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

 

Solvej
Obsidian | Level 7
Thank you Art. I will keep my requirement but thank you for the suggestion. I do however have one problem with the code. Reality is some time really difficult. I have patient journeys that are embedded in other journeys. For example one journey start 1.October and ends 30.October. This is this patients journey nr 10. Then this patient can also have journey on the 10 Oct to 10 Oct and this will also be number 10. I would prefere to not have these. Any suggestions?
art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 919 views
  • 3 likes
  • 3 in conversation