How to get these flags under the following scenarios

[SerenaJJ]

How to get these flags under the following scenarios:

Flag = "Y" if meet one of the following scenarios:
1. There is no AVALC = 'N' after the AVALC='Y' then flag should be the last non-missing record .
2. Subject ended with two consecutive missing ADY , then should flag the last AVALC = 'Y' prior to missed ADY.
4. Subject ended with one missing ADY and ADY is the last one, then should flag the last AVALC = 'Y' prior to missed ADY.

data have;
input usubjid $ ADY AVALC $;
datalines;
A 15 Y
A 36 Y
A 57 Y
A 63 Y
A 64 Y
A . N
B 15 Y
B 16 Y
B 17 Y
B . N
B . N
B . N
B 25 N
B . N
B 29 Y
B 30 Y
B 31 Y
B . N
c 17 Y
c 18 Y
c . N
c . N
D 11 Y
D . N
D 13 Y
D 14 Y
D 15 Y
E 1 N
E 2 N
E 3 N
E 4 Y
;
run;  

Records: A 64 Y ; B 17 Y;c 18 Y;D 15 Y;E 4 Y are flagged. Thank you so much!

[mkeintz]

This sounds a lot like a homework assignment.

Even if that is not the case, let us know how you think about this task, and what you have tried.  Help us to help you master the problem.

[SerenaJJ]

I try to use the find ti position different AVALC based on the rules, but looks like not a good idea

proc sort data=have out=ahve_1_4 (keep=usubjid  ADY AVALC );

by usubjid ;

run;

data have_2_4;

 set have_1_4;

  if AVALC= "Y" then do;AVALC_1 ="Y";seq=0;end;

  if AVALC= "N" then do;AVALC_1 ="N";seq=1;end;

  if AVALC= "N" and ADY = .  then do;AVALC_1 ="M";seq=2;end;

run;

proc sort ;

by usubjid ;

run;

data have_3_4;;

set have_2_4;

by USUBJID ;

retain p .;

if first.usubjid then p=1;

else p+1;

run;

proc transpose data=have_3_4 out=have_5;

by usubjid;

var AVALC_1 ;

run;

data have_6;

set have_5;

/*length col $800.;*/

col=catx(",",col1, col2,col3, col4, col5,col6 ,col7, col8, col9, col10,col11,col12,col13,col14);

/*if col NOT in (&rvar) then delete;*/

keep usubjid col;

run;

data have_7;

set have_6;

p1 = findc(col,',','i',find(col,'Y,M','i'));

p2 = findc(col,',','i',find(col,',M','i'));

p3 = findc(col,',','i',find(col,',N','i'));

p4 = findc(col,',','i',find(col,'M,M','i'));

p5 = findc(col,',','i',find(col,'N,Y','i'));

p6 = findc(col,',','i',find(col,',Y','i'));

p7 = length(col) - length(scan(col, -1, 'N') );

run;

[SerenaJJ]

Thank you for your reply. I try to use the find to position different AVALC based on the rule, but looks like not a good idea



proc sort data=have out=ahve_1_4 (keep=usubjid ADY AVALC );
by usubjid ;
run;

data have_2_4;
set have_1_4;
if AVALC= "Y" then do;AVALC_1 ="Y";seq=0;end;
if AVALC= "N" then do;AVALC_1 ="N";seq=1;end;
if AVALC= "N" and ADY = . then do;AVALC_1 ="M";seq=2;end;
run;

proc sort ;
by usubjid ;
run;

data have_3_4;;
set have_2_4;
by USUBJID ;
retain p .;
if first.usubjid then p=1;
else p+1;
run;

proc transpose data=have_3_4 out=have_5;
by usubjid;
var AVALC_1 ;
run;



data have_6;
set have_5;
/*length col $800.;*/
col=catx(",",col1, col2,col3, col4, col5,col6 ,col7, col8, col9, col10,col11,col12,col13,col14);
/*if col NOT in (&rvar) then delete;*/
keep usubjid col;
run;

data have_7;
set have_6;
p1 = findc(col,',','i',find(col,'Y,M','i'));
p2 = findc(col,',','i',find(col,',M','i'));
p3 = findc(col,',','i',find(col,',N','i'));
p4 = findc(col,',','i',find(col,'M,M','i'));
p5 = findc(col,',','i',find(col,'N,Y','i'));
p6 = findc(col,',','i',find(col,',Y','i'));
p7 = length(col) - length(scan(col, -1, 'N') );

run;