topic Re: Number of months since value x in SAS Programming

Number of months since value x

Adnan_Razaq — Fri, 09 Jun 2017 15:58:59 GMT

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

Re: Number of months since value x

PeterClemmensen — Sun, 11 Jun 2017 07:49:39 GMT

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;

Re: Number of months since value x

ballardw — Fri, 09 Jun 2017 16:12:14 GMT

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

Re: Number of months since value x

Astounding — Fri, 09 Jun 2017 22:56:38 GMT

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;

Re: Number of months since value x

PeterClemmensen — Sun, 11 Jun 2017 07:50:08 GMT

@Astounding, of course 🙂

Re: Number of months since value x

Adnan_Razaq — Tue, 13 Jun 2017 10:43:57 GMT

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