trying to explain correctly

[tmm]

I am trying so hard to explain this correctly. This table just has multiple scenarios and I am doing my best.

Ok Maybe this will help. I have a table of 125k+ rows all with the ID, dos, disch date and place of service. There are various options within this table.

I went thru all 125k+ rows to find all the options. I need to categorize all hospitals in one hospital table but if they are associated with a hospice, rehab, nursing, skilled admission then they are to go to an other table, only if they are within a 30day period. Then if there is only a hospice, rehab, nursing, skilled admission...they need to be in the other table. I am using 999 as an example. 999 has a hospital with disch 15jan2010 but then another hospital admission 20feb2010 which is less than the 30days but because the hospital is followed by another hospital, the first hospital would fall into the hospital table. The second hospital is associated with the rehab and skilled so all 3 need to be in the other table. I hope this is enough information. This is my third time trying to explain this correctly.

data have;

informat dos disch date8.;

format dos disch date8.;

input id plc $ dos disch ;

cards;

111 hospital 15jun2010 18jun2010

111 hospital 11dec2010 15dec2010

111 hospice  15dec2010 17dec2010

111 rehab    15jan2011 31jan2011

111 hospital 12nov2011 15nov2011

222 hospital 01jan2010 10jan2010

333 hospital 01feb2011 07feb2011

444 hospital 01jun2010 07jun2010

444 rehab    07jun2010 15jun2010

444 nursing  15jun2010 17jun2010

444 hospital 17aug2011 30aug2011

555 rehab    15aug2011 29aug2011

666 hospital 01jan2010 02jan2010

777 skilled  06aug2011 15aug2011

888 hospice  10aug2011 30aug2011

999 hospital 01jan2010 15jan2010

999 hospital 20feb2010 25feb2010

999 rehab    27feb2010 28feb2010

999 skilled  03mar2010 07mar2010

001 hospital 20aug2010 25aug2010

001 hospital 15sep2011 15sep2011

002 rehab    05sep2011 07sep2011

002 hospital 08sep2011 15sep2011

002 hospice  15sep2011 17sep2011

003 hospital 01jan2010 15jan2010

004 rehab    15jan2010 17jan2010

005 hospital 01jan2011 15jan2011

005 rehab    27jan2011 30jan2011

;

run;

I need to have hospital only items that are never associated with rehab, hospice, skilled put into one table and the rest in the other table. So the outcome of this above should be like this: using up to +30 between the disch and the adm. So in the case of ID 111 if they are discharged from the hospital on 15dec2010 and then admitted to rehab on 15jan2011, the entire record fo 111 during the date periods would be in the other table.

HOSPITAL Table:

111 hospital 15jun2010 18jun2010

111 hospital 12nov2011 15nov2011

222 hospital 01jan2010 10jan2010

333 hospital 01feb2011 07feb2011

444 hospital 17aug2011 30aug2011

666 hospital 01jan2010 02jan2010

999 hospital 01jan2010 15jan2010

001 hospital 20aug2010 25aug2010

001 hospital 15sep2011 15sep2011

003 hospital 01jan2010 15jan2010

OTHER Table:

data have;

informat dos disch date8.;

format dos disch date8.;

input id plc $ dos disch ;

cards;

111 hospital 11dec2010 15dec2010

111 hospice  15dec2010 17dec2010

111 rehab    15jan2011 31jan2011

444 hospital 01jun2010 07jun2010

444 rehab    07jun2010 15jun2010

444 nursing  15jun2010 17jun2010

555 rehab    15aug2011 29aug2011

777 skilled  06aug2011 15aug2011

888 hospice  10aug2011 30aug2011

999 hospital 20feb2010 25feb2010

999 rehab    27feb2010 28feb2010

999 skilled  03mar2010 07mar2010

002 rehab    05sep2011 07sep2011

002 hospital 08sep2011 15sep2011

002 hospice  15sep2011 17sep2011

004 rehab    15jan2010 17jan2010

005 hospital 01jan2011 15jan2011

005 rehab    27jan2011 30jan2011

[PaigeMiller · Accepted solution]

This is a difficult problem, and if you are having trouble explaining it, then you can't begin to program it.

Assuming you have explained it properly, here's an outline of how I would attack this

1. sort by ID and dos
2. In a data step, assign a unique (sequential) hospital admission number (I am assuming that each hospital admission in the table is unique, and then the other admissions that follow that don't say hospital are related to the previous hospital admission)
3. Within each unique hospital admission number, find the dos for the hospital admission and the minimum dos for each hospice, skilled, rehab, nursing, etc. This can probably done via PROC MEANS or PROC SUMMARY
4. recombine all of this so that each unique hospital admission number also has the minimum dos for each hospice, skilled, etc. on the same row so you can see if the subsequent admission was within 30 days
5. once step 4 is complete, this tells you how to allocate each row of the original table (actually the table from step 2)