Below code ...some how not working if I add act_alloc(date) /* Base data */ proc sql; create table test as select a.coll_idnn_hk,a.MAT_CLREF ,a.END_OF_MONTH,b.ACT_CODE,b.act_alloc from HK_MONTH_DAT as a left join com_HK as b on a.coll_idnn_hk=b.coll_idnn_hk and a.END_OF_MONTH= b.END_OF_MONTH where MAT_CLREF ne ' ' order by a.COLL_IDNN_HK,a.MAT_CLREF,b.act_alloc; quit; /**/ /*proc sort data=test out= x1;by coll_idnn_hk MAT_CLREF act_alloc ;run; */ proc sql; create table temp(drop=t) as select COLL_IDNN_HK,MAT_CLREF,ACT_CODE>' ' as t,min(END_OF_MONTH) as _min format=date9., max(END_OF_MONTH) as _max format=date9. from test group by COLL_IDNN_HK,MAT_CLREF,t having t; quit; data want; merge test temp; by COLL_IDNN_HK MAT_CLREF ; /*if (act_code>' ' and END_OF_MONTH=_min) or (act_code>' ' and END_OF_MONTH=_max) or*/ /*_min<END_OF_MONTH<_max;*/ retain _mm; length _mm $10; if first.MAT_CLREF then call missing(_mm); if not missing(act_code) then _mm=act_code; else act_code=_mm; drop _:; run; proc sort data=want1 out= c1; by coll_idnn_hk end_of_month act_alloc act_code ;run;
... View more