Retain function

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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!

MonthAcctDORMANTCOUNT
112300
212300
312311
412312
512313
612314
712315
812316
912300
1012300
1112311
1212312
1312313
1412314
145600
245611
345612
445613
545614
645615
745616

Accepted Solutions
Solution
‎03-20-2014 03:09 PM
Super User
Posts: 19,822

Re: Retain function

Posted in reply to SasGuy614

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;

View solution in original post


All Replies
Solution
‎03-20-2014 03:09 PM
Super User
Posts: 19,822

Re: Retain function

Posted in reply to SasGuy614

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 233 views
  • 0 likes
  • 2 in conversation