HI Ksharp, i have made some changes to script but I'm still getting more than 1 site Id any suggestions how I can have it all all site Id in 1 row ? %macro r(x,n);&x. as &x.&n.%mend; proc sql; create table want3 as select a.site_id , a.%r(machine,1),a.%r(start_time,1),a.%r(end_time,1),a.%r(dur_time,1), b.%r(machine,2),b.%r(start_time,2),b.%r(end_time,2),b.%r(dur_time,2) from Report4 as a inner join report4 as b on a.site_id=b.site_id where (a.end_time GT b.start_time and a.end_time LT b.end_time ) or (a.end_time EQ b.end_time and INPUT(SUBSTR(a.machine,3,4),4.) LT INPUT(SUBSTR(b.machine,3,4),4.) ); quit; proc sort data=Want3;by site_ID machine1 descending machine2;run; proc sql noprint; create table tmp as select site_ID, machine1,count(*) as cnt from want3 group by 1,2; select max(cnt) into:mx from tmp; quit; data tmp(drop=i); format mac st et dh allvar $100.; do i=2 to &mx.+1; mac=compress("machine"||i); st=compress("start_time"||i); et=compress("end_time"||i); dh=compress("dur_time"||i); allvar=compress(mac)||" "||compress(st)||" "|| compress(et)||" "||compress(dh); output; end; run; proc sql noprint; select mac into:mac separated by ' ' from tmp ; select st into:st separated by ' ' from tmp ; select et into:et separated by ' ' from tmp ; select dh into:dh separated by ' ' from tmp ; select allvar into:allvar separated by ' ' from tmp ; quit; data report6(drop=i n mac st et dh) ; retain site_id machine1 start_time1 end_time1 dur_time1 &allvar. n; format &mac. $10. &st. &et. datetime.; set want3(rename=(machine2=mac start_time2=st end_time2=et dur_time2=dh)); by site_ID machine1 ; array m{*} &mac.; array s{*} &st.; array e{*} &et.; array d{*} &dh.; if first.site_ID or first.machine1 then do; n=0; do i=1 to &mx.; m(i)="";s(i)=.;e(i)=.;d(i)=.; end; end; n+1; m(n)=mac; s(n)=st; e(n)=et; d(n)=dh; if last.site_id or last.machine1 then output report6; run;
... View more
