Help using Base SAS procedures

How to group by siteid and date

Reply
Regular Contributor
Posts: 240

How to group by siteid and date

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

Respected Advisor
Posts: 3,896

Re: How to group by siteid and date

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.

 

Regular Contributor
Posts: 240

Re: How to group by siteid and date

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

Respected Advisor
Posts: 3,896

Re: How to group by siteid and date

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;

Regular Contributor
Posts: 240

Re: How to group by siteid and date

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

Ask a Question
Discussion stats
  • 4 replies
  • 359 views
  • 3 likes
  • 2 in conversation