Solved
Contributor
Posts: 25

# Number of months since value x

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.

Accepted Solutions
Solution
‎06-13-2017 05:37 AM
PROC Star
Posts: 1,283

## Re: Number of months since value x

[ Edited ]
``````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;
``````

All Replies
Solution
‎06-13-2017 05:37 AM
PROC Star
Posts: 1,283

## Re: Number of months since value x

[ Edited ]
``````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;
``````
Super User
Posts: 6,781

## Re: Number of months since value x

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;

PROC Star
Posts: 1,283

## Re: Number of months since value x

@Astounding, of course

Contributor
Posts: 25

## Re: Number of months since value x

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

Super User
Posts: 13,563