topic Re: remove entire record in SAS Procedures

remove entire record

tmm — Wed, 11 Jul 2012 18:26:02 GMT

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

Re: remove entire record

Astounding — Wed, 11 Jul 2012 20:59:40 GMT

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.

Re: remove entire record

tmm — Wed, 11 Jul 2012 21:54:41 GMT

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.

Re: remove entire record

tmm — Wed, 11 Jul 2012 21:55:37 GMT

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.

Re: remove entire record

Reeza — Wed, 11 Jul 2012 22:07:16 GMT

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.

Re: remove entire record

tmm — Wed, 11 Jul 2012 22:38:00 GMT

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

Re: remove entire record

Reeza — Wed, 11 Jul 2012 22:48:05 GMT

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.

Re: remove entire record

Ksharp — Thu, 12 Jul 2012 03:19:43 GMT

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

Re: remove entire record

Astounding — Thu, 12 Jul 2012 13:57:28 GMT

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;

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.

Re: remove entire record

Astounding — Thu, 12 Jul 2012 14:04:51 GMT

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!