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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.