I want immediate non missing observation value after last flag is Y. that is: visit 3rd of id A1001 and visit 4th of A1002
data x;
infile cards missover;
input id $ visit val flag $;
cards;
A1001 1 20 Y
A1001 2 20 Y
A1001 3 20
A1001 4 .
A1001 5 20
A1002 1 20
A1002 2 20 Y
A1002 3 .
A1002 4 20
;
Not sure what the output should look like but something like:
data want; set x; by id; retain tmp_flg; if first.id then tmp_flg=0; if flag="" and tmp_flag then tmp_flag=1; else if tmp_flg=1 and val ne . then output; run;
Not tested, but that should work.
There was a typo:
data x; infile cards missover; input id $ visit val flag $; cards; A1001 1 20 Y A1001 2 20 Y A1001 3 20 A1001 4 . A1001 5 20 A1002 1 20 A1002 2 20 Y A1002 3 . A1002 4 20 ; run; data want; set x; by id; retain tmp_flg; if first.id then tmp_flg=0; if flag="Y" then tmp_flg=1; if flag="" and tmp_flg=1 and val ne . then do; tmp_flg=2; final="Y"; end; run;
Yes, but you didn't specify what the output should look like in your post, hence I was guessing. Replace this line:
final="Y";
With:
output;
data x;
infile cards missover;
input id $ visit val flag $;
cards;
A1001 1 20 Y
A1001 2 20 Y
A1001 3 20
A1001 4 .
A1001 5 20
A1002 1 20
A1002 2 20 Y
A1002 3 .
A1002 4 20
;
run;
data want;
do i=1 by 1 until(last.id);
set x;
by id;
if flag='Y' then _i=i;
end;
n=0;
do j=1 by 1 until(last.id);
set x;
by id;
if j gt _i and not missing(val) then n+1;
if n=1 and not missing(val) then output;
end;
drop i j n;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.