Help using Base SAS procedures

Resetting retained values with by variables

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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: 17,837

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;

View solution in original post


All Replies
Super User
Posts: 10,500

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: 17,837

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.

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

Discussion stats
  • 3 replies
  • 173 views
  • 3 likes
  • 3 in conversation