Number of months since value x

[Adnan_Razaq]

Hi,

I have the following dataset

ID	Month	Arrears
1	Jan	0
1	Feb	0
1	Mar	1
1	Apr	2
1	May	2
2	Jan	0
2	Feb	1
2	Mar	1
2	Apr	0
2	May	0

What I need is to create a flag that counts the number of months since an account was 1 plus in arrears until it returns to 0.

ID	Month	Arrears	Flag
1	Jan	0	0
1	Feb	0	0
1	Mar	1	1
1	Apr	2	2
1	May	2	3
2	Jan	0	0
2	Feb	1	1
2	Mar	1	2
2	Apr	0	0
2	May	0	0

As always any help would be greatly appreciated.

Adnan

[PeterClemmensen · Accepted solution]

data have;
input ID$ Month$ Arrears;
datalines;
1 Jan 0
1 Feb 0 
1 Mar 1
1 Apr 2
1 May 2
2 Jan 0
2 Feb 1
2 Mar 1
2 Apr 0
2 May 0
;

proc sort data = have;
   by ID;
run;

data want;
   set have;
   by ID;
   if first.ID then flag = 0;
   if Arrears > 0 then flag + 1;
   else if Arrears = 0 then flag = 0;
run;

[Astounding]

While this solution is close, I think you need to tweak it.  This statement should be added after the BY statement:

if first.id then flag=0;

[PeterClemmensen]

@Astounding, of course 🙂

[Adnan_Razaq]

Thanks for providing the solution.

I have another query stemming of the one raised.

I need to set up a count of months since the account was last 1+ in arrears. How can I set up a flag to do the following

Acc    Date     Arrears      Since last 1+

1         Jan        0                    0

1         Feb        1                    0

1         Mar        2                    0

1         Apr         0                    1

1         Jun         0                    2

1         Jul          1                    0

1         Aug         0                   1  

Many Thanks

Adnan

[ballardw]

Without a year value I expect this process to have issues unless your data order is maintained very rigorously AND is never sorted.