BookmarkSubscribeRSS Feed
SerenaJJ
Obsidian | Level 7

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!

3 REPLIES 3
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
SerenaJJ
Obsidian | Level 7

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
Obsidian | Level 7
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 572 views
  • 0 likes
  • 2 in conversation