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
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.
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.
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.
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!
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.
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.
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
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: