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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.