topic How to group by siteid and date in SAS Procedures

How to group by siteid and date

BETO — Mon, 22 Dec 2014 05:17:46 GMT

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

Re: How to group by siteid and date

Patrick — Mon, 22 Dec 2014 05:29:40 GMT

You will need to post some representative sample data and then explain how the result should look like. If you want us to actually look into your code and amend it then you should prepare your code in a way that it runs with the sample data provided.

Re: How to group by siteid and date

BETO — Mon, 22 Dec 2014 15:52:37 GMT

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

Re: How to group by siteid and date

Patrick — Mon, 22 Dec 2014 23:36:32 GMT

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;

Re: How to group by siteid and date

BETO — Thu, 08 Jan 2015 15:26:53 GMT

THanks Patrick I will use the daily pull to get my week