Add flag after certain value

[sandyzman1]

Hi,

I want to add the flag column in the following table:

id	Visit	Flag
1	1	1
1	2	1
1	5	0
1	4	x
1	3	x
2	1	1
2	2	1
2	3	1
2	5	0
2	4	x
3	1	1
3	5	0
3	2	x
3	3	x
3	4	x

I have the two columns for patient id and the number of clinic visits. The BMI was measured on each visit (1-5), and visit 5 is the final diagnosis of disease was made. There were few visits that were made after the final diagnosis, which is after visit 5. I wanted to flag 1 for prior diagnosis visit, flag 0 for diagnosis visit (5), and X for those visits after diagnosis (5). Would any please help me add a flag column to the above table?

Thanks.

[mklangley · Accepted solution]

Here's one way:

data have;
    input id visit;
    datalines;
1 1
1 2
1 5
1 4
1 3
2 1
2 2
2 3
2 5
2 4
3 1
3 5
3 2
3 3
3 4
    ;
run;

data want;
    set have;
    by id;
    retain after;
    if first.id
        then after = 0;
    if visit = 5 
        then do;
            flag = '0';
            after = 1;
        end;
    else if after = 1
        then flag = 'X';
    else flag = '1';
    drop after;
run;

[sandyzman1]

Hi mklangley,

Thank you so much. It worked. 🙂