Assuming I understand your question . data have; infile cards truncover expandtabs; input ID Flag (_date relative_month1 relative_month2 relative_month3) ($); date=input(cats('01.',_date),ddmmyy12.); format date yymmn6.; drop _date; cards; 1 1 1.2014 m1 1 0 2.2014 m2 1 0 3.2014 m3 1 1 4.2014 m4 m1 1 0 5.2014 m5 m2 1 0 6.2014 m6 m3 1 1 7.2014 m7 m4 m1 1 0 8.2014 m8 m5 m2 1 0 9.2014 m9 m6 m3 1 0 10.2014 m10 m7 m4 2 1 2.2014 m1 2 0 3.2014 m2 2 0 4.2014 m3 2 1 5.2014 m4 m1 2 0 6.2014 m5 m2 2 0 7.2014 m6 m3 2 1 8.2014 m7 m4 m1 2 0 9.2014 m8 m5 m2 ; run; data temp; set have(keep=id date relative_month1 rename=(relative_month1=month)) have(keep=id date relative_month2 rename=(relative_month2=month)) have(keep=id date relative_month3 rename=(relative_month3=month)); retain var 1; if not missing(month); run; data temp1(index=(month)); set temp; by id notsorted; retain first ; if first.id then first=date; format first yymmn6.; run; proc sort data=temp1 out=temp2 nodupkey; by id first; run; data _null_; set temp2 end=last; if _n_=1 then call execute(catt('data want; merge ')); call execute(catt('temp1(where=(id=',id,' and first=',first,') rename=(var=_',vvalue(first),'))')); if last then call execute(';by month;drop id date first;run;'); run; ... View more