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
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;
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;
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;
@Astounding, of course 🙂
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
Without a year value I expect this process to have issues unless your data order is maintained very rigorously AND is never sorted.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.