Hi @SASPreK ,your requirement should be done in several steps using different techniques, it would not be able to achieve in a single step through a single technique (e.g., I tried many times with retain and if.first /if.last techniques but failed and almost give up this question and then switched to other techniques). The steps basically include the follows: (a) produce the final results column according to your requirements, this can be done with proc transpose and array, and (b) output the last id and year row together with the final results column, this can be done though if.last within a data step and then a sql join. The code and results are as follows.
data have;
input id $ dt:yymmdd10. year test $
result $;
format dt date11.;
datalines;
ABCDE 20100429 2010 T1 N
ABCDE 20100712 2010 T1 N
ABCDE 20101025 2010 T1 Y
ABCDE 20110509 2011 T1 Y
ABCDE 20110909 2011 T1 Y
ABCUK 20150301 2015 T2 Y
ABCUK 20150801 2015 T1 N
ABCUK 20150914 2015 T1 N
ABCGH 20190613 2019 T1 N
ABCGH 20190815 2019 T2 N
ABCPK 20130409 2013 T1 N
ABCPK 20131021 2013 T1 N
;
run;
proc print data=have;run;
/*produce final results column according to
requirements, using transpose and array*/
proc sort data=have out=have1;
by id dt;
run;
proc print data=have1;run;
proc transpose data=have1 out=have2;
by id;
var result;
run;
proc print data=have2;run;
data have3;
set have2;
array rst[5] $ col1-col5;
do i=1 to 5;
if rst[i]='Y' then result='Y';
end;
run;
proc print data=have3;run;
/*output the last id and year row together
with the final results, using if.last in
a data step and sql join*/
data have4;
set have1;
by id year;
if last.year;
drop result;
run;
proc print data=have4;run;
proc sql;
select h4.*,
h3.result
from have4 as h4 left join
have3 as h3
on h4.id=h3.id;
quit;
... View more
