Solved
Contributor
Posts: 29

# Retain function

Hello All - I am trying to create a count field based upon the dormant status on the account. I'm trying to use the retain statement along with a count statement. The new field should be calculated as follows: If dormant = 1 and acct = lag(acct) .....if this is meet then count value should start at 1 and increase by 1 for every time the condition is meet.

This is what I have so for and it's getting me close but not exactly where I need to be. The count field doesn't increase by 1 for each occurrence.  Instead I am just getting a value of 1 for every time the condition is meet.

data Prep;

set Base;

retain Count;

Count + 1;

if DORMANT = 1 and acct = lag(acct) then Count = 1; else Count = 0;

run;

Thanks!

 Month Acct DORMANT COUNT 1 123 0 0 2 123 0 0 3 123 1 1 4 123 1 2 5 123 1 3 6 123 1 4 7 123 1 5 8 123 1 6 9 123 0 0 10 123 0 0 11 123 1 1 12 123 1 2 13 123 1 3 14 123 1 4 1 456 0 0 2 456 1 1 3 456 1 2 4 456 1 3 5 456 1 4 6 456 1 5 7 456 1 6

Accepted Solutions
Solution
‎03-20-2014 03:09 PM
Super User
Posts: 23,700

## Re: Retain function

You're better off using BY processing rather than trying to make sure you're on the same account.

data have;

input Month    Acct    DORMANT    COUNT;

cards;

1    123    0    0

2    123    0    0

3    123    1    1

4    123    1    2

5    123    1    3

6    123    1    4

7    123    1    5

8    123    1    6

9    123    0    0

10    123    0    0

11    123    1    1

12    123    1    2

13    123    1    3

14    123    1    4

1    456    0    0

2    456    1    1

3    456    1    2

4    456    1    3

5    456    1    4

6    456    1    5

7    456    1    6

;

run;

data want;

set have;

by acct dormant notsorted;

retain count_new;

if first.dormant and dormant=1 then count_new=1;

else if dormant=1 then count_new+1;

else count_new=0;

run;

All Replies
Solution
‎03-20-2014 03:09 PM
Super User
Posts: 23,700

## Re: Retain function

You're better off using BY processing rather than trying to make sure you're on the same account.

data have;

input Month    Acct    DORMANT    COUNT;

cards;

1    123    0    0

2    123    0    0

3    123    1    1

4    123    1    2

5    123    1    3

6    123    1    4

7    123    1    5

8    123    1    6

9    123    0    0

10    123    0    0

11    123    1    1

12    123    1    2

13    123    1    3

14    123    1    4

1    456    0    0

2    456    1    1

3    456    1    2

4    456    1    3

5    456    1    4

6    456    1    5

7    456    1    6

;

run;

data want;

set have;

by acct dormant notsorted;

retain count_new;

if first.dormant and dormant=1 then count_new=1;

else if dormant=1 then count_new+1;

else count_new=0;

run;

Contributor
Posts: 29

## Re: Retain function

Thanks - I didn't even think about doing it this way.

🔒 This topic is solved and locked.