In my code below I am rolling up dates of service that are less than 7 days apart into 1 stay for each ID and creating a new date span with my span_begin and span_end variables based off of the first and last date of service. My flag is a stay indicator variable that identifies the final stay dates I want to use in my final analysis. My code below almost does what I want but I noticed that some clients who have back to back stays in different clinics are not getting flagged or the date spans the way I want them to. For example (see code below), I need ID "C" to have 2 stays/flags, one for clinic 3 and one for clinic 4 so I need to include the clinic in the code but was unable to do this without making a mess of things.
Note:
Each ID can have more than one stay in the same clinic and/or in a different clinic.
All dates of service <7 days apart are rolled up into one stay for each ID unless they are moved into a different clinic. If they move into a new clinic that would count as a new stay with a new span_begin and span_end.
FDOS=first date of service
LDOS=last data of service
These are the results I am getting:
This is what I want:
Data Have;
input ID$ Clinic$ FDOS :DATE9. LDOS :DATE9.;
format FDOS MMDDYY10. LDOS MMDDYY10. ;
datalines;
A 1 22Jul2022 19Sep2022
A 1 20Sep2022 19Oct2022
A 1 20Oct2022 24Oct2022
B 2 1Jul2022 1Jul2022
B 2 2Jul2022 15Jul2022
B 2 16Jul2022 22Jul2022
B 2 23Jul2022 23Jul2022
B 2 24Jul2022 31Jul2022
B 2 1Aug2022 15Aug2022
B 2 16Aug2022 30Aug2022
B 2 31Aug2022 31Aug2022
B 2 1Sep2022 2Sep2022
B 2 3Sep2022 4Sep2022
B 2 5Sep2022 15Sep2022
B 2 16Sep2022 29Sep2022
C 3 6Mar2023 31Mar2023
C 3 1Apr2023 4Apr2023
C 3 5Apr2023 30Apr2023
C 3 1May2023 4May2023
C 3 5May2023 31May2023
C 3 1Jun2023 3Jun2023
C 3 4Jun2023 7Jun2023
C 4 8Jun2023 30Jun2023
D 5 1Jul2022 15Jul2022
E 5 5Apr2023 15Apr2023
E 5 16Apr2023 30Apr2023
E 5 1May2023 4May2023
;
run;
Proc sort data=Have;
by ID FDOS LDOS;
run;
data Span;
set Have;
by ID FDOS LDOS;
retain Span_Begin;
l_last = lag(LDOS);
if first.ID then Span_Begin=FDOS;
else if FDOS - l_last > 7 then do;
Span_Begin=FDOS;
end;
Span_End=LDOS;
format Span_Begin mmddyy10. Span_End mmddyy10. ;
run;
proc sort data=Span;
by ID Span_Begin Span_End;
run;
data Flag;
set Span;
by ID Span_Begin Span_End;
flag=last.Span_Begin;
run;
proc print data=Flag;
var ID Clinic Span_Begin Span_End flag ;
run;
Sort the data by Id Clinic then the dates.
Add Clinic to the BY statement in the data step.
Do the spans based on first.Clinic instead of first.Id
Sort the data by Id Clinic then the dates.
Add Clinic to the BY statement in the data step.
Do the spans based on first.Clinic instead of first.Id
@Whitlea wrote:
Is there a way to accomplish this while keeping the order of ID ,FDOS,LDOS?
Probably.
You can use BY ID CLINIC but include the NOTSORTED keyword in the BY statement to let the data step know that the values of CLINIC might not be strictly increasing.
If you find that some was in CLINIC A of Monday and B on Tuesday and then back to A on Wednesday that would be treated as 3 different stays.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.