HI i have a table that houses data like this SIte ID. Machine 1. startdate1. Enddate1. Machine 2. Stardate 2. endDate 2. Machine3. a startdate3 enddate3 1. A1. 12/12/14. 12/12/14. A2. 12/13/14. 12/13/14. A2. 12/12/14. 12/12/14 eeverything is perfect except the 12/13/14 should be on its own line not the same. The report ran great when it was daily but was requested to run weekly now it's group every entry by site Id an not seprateing starttime %Marco r(x,n);&x. As &x. &n. %mend; proc sql; create table want3 as select a.site Id ,a.total, a %r (machine,1) ,a%r(start_time,1),a%r(end time,1), a.%r( dur time ,1), a.total, 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. Siteid =b. Site id where ( a. End time gt b.start time and a end time Lt b.endtime ) or (a. end time eq b. Endtime aand input ( substr( a.machine,3,4),4) Lt input ( substr( b.machine,3,4),4) ); quit; proc roc sort data=want3 ;by site I'd machine1 descending machine2 run; proc sql no print ;create table tmp as select site I'd ,total, machine ,count(*) as cnt from want3 group by 1,2; select max (cnt) into:mx from tmp; quit; data tmp(drop=I) ; format at mac still et dh allvara $100.; do I=2 to &mx. +1 ; mac= compress("machine"||i); st=compress ("starttime"||i); et= compress ("endtimer"||i); dh=compress ("dur time"||i); allvara = compress (Mac)|| " " || compress (st) || ""|| compress (et) ||""|| compress (dh); out put; end; run; proc sql no print ; select Mac into :Mac separated by ' ' From tmp; select st into: st seperated by'' ". From tmp; select et into: et seperated by" " from tmp; select dh into: dh seperated by" " from tmp; select allvara into : allvara seperated by ". " from tmp; quit; Data report 6 (drop =I n Mac st et dh); retain in site id total maxhine1 start date1 endtime1 durtime1 &allvara. N; format &mac. $10 &st. &et. Date time.; Set want3 ( rename= machine2= Mac starttime2=st endtime2=et durhr2 =dh)); by siteid machinwe1; array m(*) &mac. ; array s(*) &st.; array e(*) &et.; array d(*)&ad. ; if first.siteid or first machine1 then do ;n=0 ; do;I=1 to &mx. ; m(I) = " " ;s( i ) = .;e(i) =. ; d(i) = .; end; n+1 ; m(n)=mac; s(n) =st; e(n)=et; d(n)= dh; if last.sitid or last.machine1 then output report6 run; this his works when it's a single day but when I pull a weekly report it combines all the dates into one row by site I'd ..thanks for your help I type it up quick my battery is losing charge so please excuse typos
... View more
