Solved
Contributor
Posts: 28

# Resetting retained values with by variables

I have a longitudinal data set with rows for a member||visit. I am interested in creating flag by each memberid. In particular,  I am looking to create a new flag (flag2) that is based on a certain value of flag1 (in sample data set). I want flag2 to be 1 the first time it sees flag1 = 3 and then kept at 1 for all remaining records for the same memberid. E.G. there may be values other than 3 in subsequent visits for that member, but I still want that member to have flag2 = 1 since it is a record after the first value of flag1 = 3. I've tried using various iterations of retain, sum, etc. and am not having any luck. Would appreciate any thoughts. Please see data below:

data test;

input memberid visit flag1;

datalines;

123    1 0

123    2 0

123 3 1

123 4 0

123 6 3

456 1 3

456 2 7

456 3 0

789 1 0

789 2 1

789 3 2

789 4 0

789 5 3

789 6 0

;

run;

proc sort data=test;

by memberid;

run;

data test2;

set test;

by memberid ;

flag2 = (flag1 = 3);

*but also want for all subsequent observations for that member;

run;

So the data set I want is:

memberid    visit  flag1 flag2

123           1      0      0

123           2      0      0

123           3      1     0

123           4      0     0

123           6      3     1

456           1      3     1

456           2      7     1

456           3      0     1

789           1      0     0

789           2      1     0

789           3      2     0

789           4      0     0

789           5      3     1

789           6      0     1

Accepted Solutions
Solution
‎05-05-2014 12:53 PM
Super User
Posts: 23,771

## Re: Resetting retained values with by variables

Would flag2 ever reset if its only checking on first.memberid?

data test2;

set test;

by memberid ;

retain flag2;

if first.memberid then flag2=.;

flag2 = max((flag1 = 3), flag2);

*but also want for all subsequent observations for that member;

run;

All Replies
Super User
Posts: 13,582

## Re: Resetting retained values with by variables

data test2;

set test;

by memberid ;

retain flag2;

if first.memberid then   flag2 = (flag1 = 3);

*but also want for all subsequent observations for that member;

run;

should do it.

Solution
‎05-05-2014 12:53 PM
Super User
Posts: 23,771

## Re: Resetting retained values with by variables

Would flag2 ever reset if its only checking on first.memberid?

data test2;

set test;

by memberid ;

retain flag2;

if first.memberid then flag2=.;

flag2 = max((flag1 = 3), flag2);

*but also want for all subsequent observations for that member;

run;

Contributor
Posts: 28

## Re: Resetting retained values with by variables

Thanks very much. The max function with the logic as input is clever. Appreciate both your responses.

🔒 This topic is solved and locked.