remove entire record

[tmm]

I have a table of 125725 records and want to put all the records into a separate table that are associated with a certain place of service. Here is an example:

id                plc of service             dos                         disch

111             nursing                     11dec2010              15dec2010

111             hospice                    15dec2010              17dec2010

111             rehab                       18dec2010              30dec2010

111            hospital                     12dec2010              01jan2011

111            hospital                     12nov2011              15nov2011

222            hospital                     01jan2010               10jan2010

333            hospital                     01feb2011               07feb2011

444            nursing                      01jun2010               07jun2010

444            rehab                         09jun2010              15jun2010

444            hospital                     16jun2010               17jun2010

444            hospital                     17aug2011              30aug2011

What I want to keep are only those ID's in a table that are not affiliated with anything other than hospital. Those ID's that have other than hospital I want to put in their own table. So I would end up with two tables and it looks like this:

table:       hospital only

id            plc of service            dos                    disch

111         hospital                   12nov2011           15nov2011

222         hospital                   01jan2010            10jan2010

333         hospital                   01feb2011            07feb2011

444         hospital                   17aug2011           30aug2011

table: others

id                plc of service             dos                         disch

111             nursing                     11dec2010              15dec2010

111             hospice                    15dec2010              17dec2010

111             rehab                       18dec2010              30dec2010

111            hospital                     12dec2010              01jan2011

444            nursing                      01jun2010               07jun2010

444            rehab                         09jun2010              15jun2010

444            hospital                     16jun2010               17jun2010

I know that 111 and 444 are associated with others but as you can see the dates that have others are really close to one another. 111 has a hospital date that is 12nov2011 that is no where near the 2010 dates and therefore is only a hospital and not associated with the other places of services. Same with the one 444. So that is what I am running into. I am not sure how query that really. I can say where dos = disch but as you can see the dos does not always equal the disch

[Astounding]

You'll have to clarify a few rules first.

The 444 hospital record belongs with the "other" group because the hospital DOS fell on the next day after an "other" discharge.  What if it fell two days later?  How about three days later?  How much time is allowed to elapse before the hospital record is considered separate?

What if the hospital discharge falls just before (rather than just after) an "other" DOS?  Does the hospital record still belong with the "other" records?  Again, how many days must pass for the hospital record to be considered separate?

Are your date variables stored as SAS dates, or as character strings?

That should be enough to guide the programming.

Good luck.

[tmm]

If the dates are within 30days of one another. In this case we can see that for instance with 444 the jun dates are all within one another. A few days so that would fall into the other but the 1 in 2011 is by itself so it would be in hospital. If the hospital is within the other dates for the other category.

They are character strings set up by the table I pulled from in our claims system.

[Astounding]

OK, here's my plan.

1. Form groupings (using the new variable EPISODE).

2. Create a format that translates the combination of ID and date range into EPISODE.

3. Apply the format to all the records, hospital records included.

Here is some code to address the first step, assuming your dates are already on SAS's date scale.

proc sort data=have;

   by id dos disch;

run;

data episodes;

   set have;

   by id;

   retain episode episode_begin_date episode_end_date;

   if first.id then do;

      episode=1;

      episode_begin_date = dos;

      episode_end_date = disch;

   end;

   else do;

      if dos <= episode_end_date + 30 then episode_end_date = disch;

      else do;

         episode + 1;

         episode_begin_date = dos;

         episode_end_date = disch;

      end;

   end;

run;

You can examine the data and see if it is grouping the services into episodes the way you would like.  If the date ranges look right to you on the final record for each episode, we can approach how to proceed from that point.

Here is one tricky situation.  If a person leaves an "other" treatment, enters the hospital, then goes back to an "other" treatment, is it still OK for 30 days to elapse in between?

Good luck.

[Astounding]

Just noticed that your data can contain date ranges that overlap.  There's a minor change to make:

if dos <= episode_end_date + 30 then episode_end_date = max(episode_end_date, disch);

Sorry about that!

[tmm]

I have no clue how to program this. That is my problem. Was thinking that I could do a = 'hospital' but not sure how I would say the date section.

[Reeza]

Here's one version of the logic that can help you code.

Create a new variable that creates episodes of care first.

If the dos is more than 30 days than the previous discharge (think lag function) then you can call this a new episode.

id                plc of service             dos                         disch           episode

111             nursing                     11dec2010              15dec2010    1

111             hospice                    15dec2010              17dec2010    1

111             rehab                       18dec2010              30dec2010    1

111            hospital                     12dec2010              01jan2011     1

111            hospital                     12nov2011              15nov2011     2

222            hospital                     01jan2010               10jan2010    1

333            hospital                     01feb2011               07feb2011    1

444            nursing                      01jun2010               07jun2010   1

444            rehab                         09jun2010              15jun2010   1

444            hospital                     16jun2010               17jun2010   1

444            hospital                     17aug2011              30aug2011  2

Then check for only hospital within the episode, you can do this within proc sql using the logic max(plc_of_service)=min(plc_of_service) and min(plc_of_service)='hospital'  to get the unique id and episodes that are

Only records that belong in are ones that have

id episode

111 2

222 1

333 1

444 2

Then use proc SQL two more times to get the data that matches the criteria above or doesn't match.

[tmm]

ok so i would code this as

proc sql;

create table want as

(select

id,

plc_of_srvc,

dos,

disch,

case when *********i am not sure

[Reeza]

I would probably code the first part as a datastep, similar to a 30 day discharge/readmit process. Certain records would be considered 30 day admit/readmit and you should have code for that lying around somewhere I'm guessing.

[Ksharp]

If there are multiple hospitals for the same id ,what you are going to  do?

In your sample data , there are only two hospitals.

Ksharp