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
TThanks Patrick
the data looks like this
siteid branch machine St et ticket. . Dur time. Total
1. Long. A1. 12/14./14.07:38 12/14/14. 07:42 345. 4. 2
1 Long. A2. 12/13/14.08:14 12/13/14. 8:20. 234. 6. 4
2. Brush. A4. 12/22/14. 07:15 12/22/14. 07:20 256. 5 6
3. Short. a5. 12/22/14. 08:28 12/22/14. 8:35. 678. 7 3
1. Long. A3. 12/14/14. 07:39. 12/14/14 07:45. 734. 6. 2
what I would like to show is this
siteid. Branch. Machine. St. Et. Ticket. Dur time
1. Long. A1. 12/14/14. 07:38 12/14/14.07:42 345 6.
1 Long. A3. 12/14/147:39. 12/14/14 07:45. 734. 2
above would be in one row becuase the purpose of the report is to I'd when 2 differ machine at same site start time cross with each other
next row would include
Since the other 3 dont cross I won't need them.
whats is happening is every instance of siteid =1 is being put on the same row so I see three diff entry
an siteid brush is put on the same row my gut feeling is because it has same site I'd an my code is not taking date into consideration ... It works great when I'm pulling for one day but when I try to combine a week total it combines every entry by site I'd ... I hope this helps thanks again
You say it works great for one day. Can you please post the code you've written for this as it can't be the one you've posted which got syntax errors in it.
Here some code which returns the wanted result based on the sample data you've provided. I haven't re-calculated the duration as in your "wanted" data as this doesn't add up on row level with the start & end times there. So to get this done you would need to fully specify what you need - and I believe if you have a summed up duration you should also add the overall start and end times there on row level.
data have;
rowid=_n_;
infile datalines truncover dlm=' ';
input (siteid branch machine) ($) (St et) (:anydtdtm.) (ticket Dur_time Total) (:best32.);
format st et datetime21.;
datalines;
1 Long A1 12/14/14:07:38 12/14/14:07:42 345 4 2
1 Long A2 12/13/14:08:14 12/13/14:8:20 234 6 4
2 Brush A4 12/22/14:07:15 12/22/14:07:20 256 5 6
3 Short a5 12/22/14:08:28 12/22/14:8:35 678 7 3
1 Long A3 12/14/14:07:39 12/14/14:07:45 734 6 2
;
run;
proc sql;
create table want as
select distinct l.*
from have l,have r
where
r.siteid=l.siteid and
r.rowid ne l.rowid and
(r.st<= l.et <= r.et or r.st<= l.st <= r.et)
order by l.siteid, l.st, l.et, l.branch, l.machine
;
quit;
THanks Patrick I will use the daily pull to get my week
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.