DATA Step, Macro, Functions and more

Need help on Loop and lag for continue enrollment counting

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Need help on Loop and lag for continue enrollment counting

Hi I would need your help with my loop and lag gap, Loop is something I am not proficient on, so I haven't make my code work for my purpose.

 

I am trying to count days of enrollment of each person in 2015, and my problem is with those who have more than one row of entries (they dropped and re-enrolled). My continuous enrollment standard is that as long as no single enrollment gap > 45 days, this person is continued enrolled (continue_enroll_flag = 1)

 

My attempt method:

  • If the person has only one row of entry, Gap = 365 - (enrollment_end - enrollment_start)
  • If the person has multiple rows:  I am thinking using code gap = enrollment_start - lag(enrollment_end)
  • Then create continuous_enroll_flag: if Gap > 45 then continuous_enroll_flag = 0; else, continuous_enroll_flag = 1;

 

Loop is something I've always messed up with, so I would like someone to help me with it... (won't post my code because I kinda don't know how to start, I am thinking something like if first.enrollment_start, maybe wrong)

 

My data looks like this:

person_idenrollment_startEnrollment_end
11/1/20153/1/2015
16/2/201512/31/2015
22/1/201512/31/2015
31/1/201512/31/2015
43/1/201512/31/2015
51/1/20157/1/2015
58/1/201512/31/2015
61/1/201510/25/2015
74/6/201512/1/2015

 

my desired output looks like this:

person_idenrollment_startEnrollment_endEnrollmentDaysEnrollmentGapContinue_enroll_Flag
11/1/20153/1/201559930
16/2/201512/31/2015212930
22/1/201512/31/2015333321
31/1/201512/31/201536411
43/1/201512/31/2015305600
51/1/20157/1/2015181311
58/1/201512/31/2015152311
61/1/201510/25/2015297680
74/6/201512/1/20152391260

 

Appreciate your help!

 


Accepted Solutions
Solution
‎10-23-2017 05:05 PM
Super User
Posts: 24,004

Re: Need help on Loop and lag for continue enrollment counting

Posted in reply to LisaYIN9309

You'll need multiple steps to do this, not one.

 

First find the lags, then find the IDs you need to erase. Then select the people you want included.

 

proc sql;
create table want as
select *
from YourData 
where ID not in (Select id from YourData where gap>45);
quit;

View solution in original post


All Replies
Super User
Posts: 24,004

Re: Need help on Loop and lag for continue enrollment counting

Posted in reply to LisaYIN9309

A data set implicitly loops so there's no need for loops here at all. Use LAG() to get the previous value when needed.

 

Contributor
Posts: 38

Re: Need help on Loop and lag for continue enrollment counting

Thank you @Reeza for your prompt reply, could you specify what do you mean by using lag?

 

I did this

I've created this multiple_enroll flag, if it ne 1, it means this person has multiple rows

data Step6_I4;

set Step6_I3;

if multiple_enroll ne 1 then

gap = enr_start_date - lag(enr_end_date);

run;

 

It gives me this:

Capture.PNG

 

How can I let SAS only calculate the gap starting from the second row of each person with multiple entries? So it won't have values like -364 shows up (it used person 1's end date and person 2's start date to calculate).

 

Thank you

Super User
Posts: 24,004

Re: Need help on Loop and lag for continue enrollment counting

Posted in reply to LisaYIN9309

LAG() cannot be calculated conditionally, or at least then it doesn't behave the way you expect. 

 

See this example here to get you started:

 

data have;
informat person_id $1. date_enroll_start date_enroll_end mmddyy10.;
format date_: date9.;
input person_id date_enroll_start date_enroll_end;
cards;
1	1/1/2015	3/1/2015
1	6/2/2015	12/31/2015
2	2/1/2015	12/31/2015
3	1/1/2015	12/31/2015
4	3/1/2015	12/31/2015
5	1/1/2015	7/1/2015
5	8/1/2015	12/31/2015
6	1/1/2015	10/25/2015
7	4/6/2015	12/1/2015
;
run;

data want;
set have;
by person_id;

lag_enroll_end = lag(date_enroll_end);

if first.person_id then gap = 365 - date_enroll_end + date_enroll_start;
else gap = date_enroll_start - lag_enroll_end;

format lag_enroll_end date9.;
run;
Contributor
Posts: 38

Re: Need help on Loop and lag for continue enrollment counting

Thank you @Reeza. this code creates the exact same problem as my old code gives me, instead of giving a negative values derived from using previous person's enrollment end date, it gives a positive value, but the same thing.

 

2.PNG

Super User
Posts: 24,004

Re: Need help on Loop and lag for continue enrollment counting

Posted in reply to LisaYIN9309

I'm not seeing those numbers based on the sample data provided so can please explain the issue in more detail?

I'm not seeing an issue, so you may need to expand your sample data to better reflect your actual data.

 

The IF/BY processing prevents some one elses data from rolling over, ie the calculation varies depending on the person's record.

Contributor
Posts: 38

Re: Need help on Loop and lag for continue enrollment counting

Thank you for asking @Reeza, sorry I should have been more clear on it. You are right, the calculation is based on person_id, so no other person's data is rolled into the calculation.

 

My problem is that I want to delete a person whose real largest single gap > 45, which means if a person has three rows, all three rows will be deleted. this won't work with this output :2.PNG

because the first gap number of this person, 214, is not the real gap.

 

i.e. (I made this up) A person's real largest single gap is 31, this person shouldn't be deleted, how should I manage it?

1/1/20157/1/2015200892045331-Dec-15183
8/1/201511/30/2015200892045331-Dec-1531

 

Is there any way to make something like:

 

1/1/20157/1/2015200892045331-Dec-1531
8/1/201511/30/2015200892045331-Dec-1531

Hope this is clear.

Thank you for constantly helping out on this!

Solution
‎10-23-2017 05:05 PM
Super User
Posts: 24,004

Re: Need help on Loop and lag for continue enrollment counting

Posted in reply to LisaYIN9309

You'll need multiple steps to do this, not one.

 

First find the lags, then find the IDs you need to erase. Then select the people you want included.

 

proc sql;
create table want as
select *
from YourData 
where ID not in (Select id from YourData where gap>45);
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 242 views
  • 0 likes
  • 2 in conversation