cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
JH74
Obsidian | Level 7 JH74
Obsidian | Level 7
Go to Solution

Identify continuous enrollment

Posted 06-30-2023 12:56 PM (2949 views)

Hello.  I need to identify continuous enrollment of at least 6 months or more for members.  The data spans multiple years, and a consecutive 6 months can be from one year to the next.  I would like a new variable to be created that has either a Y (6 consecutive months or >), or N (not conscutive 6 months or >) consecutive.  In addition, a member can have multiple periods of 6 consecutive months in the plan.  Below is how I would like the data to look after running the code to determine 6 consecutive months.  The data I have now DOES NOT have the 6_Mos_Cons variable, that's what I want created.  

 

Data example is this:

Acct_NbrCont_Mnth6_Mos_Cons
1234562022/01Y
1234562022/02 
1234562022/03 
1234562022/04 
1234562022/05 
1234562022/06 
2345672022/01N
2345672022/02 
2345672022/04 
2345672022/07Y
2345672022/08 
2345672022/09 
2345672022/10 
2345672022/11 
2345672022/12 
3456782021/10Y
3456782021/11 
3456782021/12 
3456782022/03 
3456782022/04 
3456782022/05 

 

The Y or N for the created variable can either be on the first line of a consecutive or non-consecutive span of months, or it can be on the last.  Also, if it is easier, it can be numeric, ie 1 = consecutive and 0 = non-consecutive.  

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
José_Costa_biw
Fluorite | Level 6 José_Costa_biw
Fluorite | Level 6

Re: Identify continuous enrollment

Posted 06-30-2023 02:41 PM (2915 views)  |   In reply to JH74

Hi,

I have a doubt about your requirements.

You said you wanted to find accounts with 6 consecutive months of enrollment.

In your example, it is clear why acct_nbr 123456 is a "Y", and 234567 (in 2022/07) is also a "Y".

But I fail to understand why acct_nbr 345678 is a "Y", because it has two periods with 3 consecutive months but no 6 consecutive months.

 

However, supposing I got your requirements right:

  • source table with acct_nbr and date (cont_mnth);
  • cont_mnth is a numeric (date) value;
  • there are no duplicates, meaning there will only be one record for each acct_nbr and cont_mnth (year and month);
  • source table is sorted by acct_nbr and cont_mnth,

this should do it:

 

 

data target;
	/* source table, must have acct_nbr and cont_mnth (numeric, date), must be sorted by acct_nbr and conth_mnth */
	set source; 
	by acct_nbr cont_mnth;
	/* history - ensures that consecutive acct_nbrs don't affect each other */
	if first.acct_nbr then history=0;
	history+1;
	/* usage of lag function to store and retrieve data of the 6th previous record */
	cont_mnth_lag5 = lag5(cont_mnth);
	/* this will only be true if:
			last 6 records are of current acct_nbr 
			distance between current month and month of the 6th previous record is equal to 5 months
	*/
	if history ge 6 and 5=intck('month',cont_mnth_lag5,cont_mnth)
		then '6_Mos_Cons'n = 'Y'; 

	/* target will only have required fields */
	keep acct_nbr cont_mnth '6_Mos_Cons'n; 
run;

Notice that this will only mark the last month of a 6 consecutive month streak for the same acct_nbr as "Y".

It will not mark any record with "N".

 

View solution in original post

Reply
8 REPLIES 8
José_Costa_biw
Fluorite | Level 6 José_Costa_biw
Fluorite | Level 6

Re: Identify continuous enrollment

Posted 06-30-2023 02:41 PM (2916 views)  |   In reply to JH74

Hi,

I have a doubt about your requirements.

You said you wanted to find accounts with 6 consecutive months of enrollment.

In your example, it is clear why acct_nbr 123456 is a "Y", and 234567 (in 2022/07) is also a "Y".

But I fail to understand why acct_nbr 345678 is a "Y", because it has two periods with 3 consecutive months but no 6 consecutive months.

 

However, supposing I got your requirements right:

  • source table with acct_nbr and date (cont_mnth);
  • cont_mnth is a numeric (date) value;
  • there are no duplicates, meaning there will only be one record for each acct_nbr and cont_mnth (year and month);
  • source table is sorted by acct_nbr and cont_mnth,

this should do it:

 

 

data target;
	/* source table, must have acct_nbr and cont_mnth (numeric, date), must be sorted by acct_nbr and conth_mnth */
	set source; 
	by acct_nbr cont_mnth;
	/* history - ensures that consecutive acct_nbrs don't affect each other */
	if first.acct_nbr then history=0;
	history+1;
	/* usage of lag function to store and retrieve data of the 6th previous record */
	cont_mnth_lag5 = lag5(cont_mnth);
	/* this will only be true if:
			last 6 records are of current acct_nbr 
			distance between current month and month of the 6th previous record is equal to 5 months
	*/
	if history ge 6 and 5=intck('month',cont_mnth_lag5,cont_mnth)
		then '6_Mos_Cons'n = 'Y'; 

	/* target will only have required fields */
	keep acct_nbr cont_mnth '6_Mos_Cons'n; 
run;

Notice that this will only mark the last month of a 6 consecutive month streak for the same acct_nbr as "Y".

It will not mark any record with "N".

 

Reply
JH74
Obsidian | Level 7 JH74
Obsidian | Level 7

Re: Identify continuous enrollment

Posted 06-30-2023 05:04 PM (2892 views)  |   In reply to José_Costa_biw

Thank you for your reply.  And, yes my apologies.  The correct example data is:

Acct_NbrCont_Mnth6_Mos_Cons
1234562022/01Y
1234562022/02 
1234562022/03 
1234562022/04 
1234562022/05 
1234562022/06 
2345672022/01N
2345672022/02 
2345672022/04 
2345672022/07Y
2345672022/08 
2345672022/09 
2345672022/10 
2345672022/11 
2345672022/12 
3456782021/10Y
3456782021/11 
3456782021/12 
3456782022/01 
3456782022/02 
3456782022/03 

 

So, you can see here why 345678 is a Y.  

 

Also, the Cont_Mnth field is not in date format, it's in character format.  I believe this will not allow intck to work correctly?  But I did run what you suggested and it looks like it's close to working.  I got this:

Acct_NbrCont_Mnthhistorycont_mnth_lag5cont_mnthCons_Mnth_6
1234562022/011 . 
1234562022/022 . 
1234562022/033 . 
1234562022/044 . 
1234562022/055 . 
1234562022/0662022/01. 
1234562022/0772022/02. 
1234562022/0882022/03. 
1234562022/0992022/04. 
1234562022/10102022/05. 
1234562022/11112022/06. 
1234562022/12122022/07. 
1234562022/01132022/08. 
1234562022/02142022/09. 
1234562022/03152022/10. 
1234562022/04162022/11. 
7890102022/0112022/12. 
7890102022/0222023/01. 

 

0 Likes
Reply
José_Costa_biw
Fluorite | Level 6 José_Costa_biw
Fluorite | Level 6

Re: Identify continuous enrollment

Posted 06-30-2023 06:11 PM (2865 views)  |   In reply to JH74
Hi,

In order to convert string to date, I suggest the following:

newvar = input(compress(oldvar,'/'),yymmn6.)

Just replace oldvar for the name of the variable that stores date as char yyyy/mm and newvar for the new variable to be created as numeric date. You may with to set a proper format, of course...
Reply
mkeintz
PROC Star mkeintz
PROC Star

Re: Identify continuous enrollment

Posted 06-30-2023 03:23 PM (2903 views)  |   In reply to JH74

So at the start of each ACCT_NBR, or after any gap of more than one month you want a new variable (six_mon_con) set to "Y" or "N".  It will be a "Y"  if it is followed by 5 or more consecutive months.  Otherwise "N".

 

If that is correct, then your sample expected result is wrong for 234567,2022/04 (which should be an N, not blank), 345678,2021/10 (which should be N, not Y) and 345678,2022/03 (which should be N, not blank).

 

Here a simple program that does what I expect you want:

 

data have;
  input Acct_Nbr	Cont_Mnth	:$7. @21 expected_6_mons_con $1.;
  cont_date=input(cont_mnth||'/01',yymmdd12.);
  format cont_date date9. ;
datalines;
123456	2022/01	Y
123456	2022/02	 
123456	2022/03	 
123456	2022/04	 
123456	2022/05	 
123456	2022/06	 
234567	2022/01	N
234567	2022/02	 
234567	2022/04	 
234567	2022/07	Y
234567	2022/08	 
234567	2022/09	 
234567	2022/10	 
234567	2022/11	 
234567	2022/12	 
345678	2021/10	Y
345678	2021/11	 
345678	2021/12	 
345678	2022/03	 
345678	2022/04	 
345678	2022/05	 
run;

data want (drop=_:);
  merge have (firstobs=1)
        have (firstobs=6 keep=acct_nbr cont_date rename=(acct_nbr=_ac6 cont_date=_cd6));

  if acct_nbr^=lag(acct_nbr) or intck('month',lag(cont_date),cont_date)>1      then six_mon_or_more='N';
  if six_mon_or_more='N' and _ac6=acct_nbr and intck('month',cont_date,_cd6)=5 then six_mon_or_more='Y';
run;

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reply
JH74
Obsidian | Level 7 JH74
Obsidian | Level 7

Re: Identify continuous enrollment

Posted 06-30-2023 05:14 PM (2883 views)  |   In reply to mkeintz
Thank you so much for your reply. I neglected to indicate in my original post that the fields are all Character, no date field. So, intck won't work.
0 Likes
Reply
mkeintz
PROC Star mkeintz
PROC Star

Re: Identify continuous enrollment

Posted 06-30-2023 07:50 PM (2856 views)  |   In reply to JH74
@JH74 wrote:
Thank you so much for your reply. I neglected to indicate in my original post that the fields are all Character, no date field. So, intck won't work.

Notice the I converted the character string into a date field in data set HAVE.  But it that is not an option, then:

 

data have;
  input Acct_Nbr	Cont_Mnth	:$7. @21 expected_6_mons_con $1.;
datalines;
123456	2022/01	Y
123456	2022/02	 
123456	2022/03	 
123456	2022/04	 
123456	2022/05	 
123456	2022/06	 
234567	2022/01	N
234567	2022/02	 
234567	2022/04	 
234567	2022/07	Y
234567	2022/08	 
234567	2022/09	 
234567	2022/10	 
234567	2022/11	 
234567	2022/12	 
345678	2021/10	Y
345678	2021/11	 
345678	2021/12	 
345678	2022/03	 
345678	2022/04	 
345678	2022/05	 
run;

data want (drop=_:);
  merge have (firstobs=1)
        have (firstobs=6 keep=acct_nbr cont_mnth rename=(acct_nbr=_ac6 cont_mnth=_cm6));

  _cd1=input(compress(cont_mnth,'/'),yymmn6.);
  _cd6=input(compress(_cm6,'/'),yymmn6.);
  if acct_nbr^=lag(acct_nbr) or intck('month',lag(_cd1),_cd1)>1            then six_mon_or_more='N';
  if six_mon_or_more='N' and _ac6=acct_nbr and intck('month',_cd1,_cd6)=5 then six_mon_or_more='Y';
run;

BTW, this assumes that the data are grouped by acct_nbr, and (within acct_nbr) are sorted by cont_mnth.  Also it assumes no more than one observation per month.  

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reply
JH74
Obsidian | Level 7 JH74
Obsidian | Level 7

Re: Identify continuous enrollment

Posted 06-30-2023 05:12 PM (2887 views)  |   In reply to JH74
I neglected to indicate that the Acct_Nbr and Cont_Mnth fields are CHARACTER, not DATE. Also, the last 3 records I had the wrong Cont_Mnth values. They should be 2022/01, 2022/02, and 2022/03.
0 Likes
Reply
Ksharp
Super User Ksharp
Super User

Re: Identify continuous enrollment

Posted 07-01-2023 05:23 AM (2832 views)  |   In reply to JH74 
data have;
infile datalines truncover;
  input Acct_Nbr	Cont_Mnth	:$7.  expected_6_mons_con $;
  cont_date=input(cont_mnth||'/01',yymmdd12.);
  format cont_date date9. ;
datalines;
123456	2022/01	Y
123456	2022/02	 
123456	2022/03	 
123456	2022/04	 
123456	2022/05	 
123456	2022/06	 
234567	2022/01	N
234567	2022/02	 
234567	2022/04	 
234567	2022/07	Y
234567	2022/08	 
234567	2022/09	 
234567	2022/10	 
234567	2022/11	 
234567	2022/12	 
345678	2021/10	Y
345678	2021/11	 
345678	2021/12	 
345678	2022/03	 
345678	2022/04	 
345678	2022/05	 
;
run;
proc sql;
create table want as
select *,case when 
(select count(distinct cont_date) from have 
where Acct_Nbr=a.Acct_Nbr and cont_date between a.cont_date and intnx('month',a.cont_date,5)) 
=6 then 'Y'
else 'N' end as want
 from have as a;
quit;
Reply

sas-innovate-2026-white.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!

Register now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Watch Creating Custom Steps in SAS Studio on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • ‎06-30-2023 12:56 PM
  • 2950 views
  • 5 likes
  • 4 in conversation