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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: