Schedule Compare Actual shipment Time

[BETO]

HI I need assistance on this ad hoc request .

i have two tables one has schedule and the other is the actual shipment date ...

schedule table looks like this 1= No Schedule  2=AM 3=PM

ID.    Mon.  Tue.  Wed Thur. Fri Sat sun

A1.   1.        1.       1.     2.     1.   1.   1

A2.   1.        3.        1.    1.     1.   1.    1

A3.   1.         2.       1.   1.      1.    1.    1

Second table Actual

ID.   Date.           Time.     AM/PM

A1.   08/25/14      10:00        AM

A2.    08/25/14.     13:00       PM

A3.    08/25/14.       14:00     PM

I need to look at actual table an compare it to schedule table to see if it was in compliance

ID.   Date.           Time.     AM/PM. Compliance

A1.   08/25/14      10:00        AM      YES

A2.    08/25/14.     13:00       PM      YES

A3.    08/25/14.       14:00     PM       NO

Thanks

[LinusH]

Transpose table one.

Calculate DOW on table two.

Make sure both tables use the same coding for DOW and AM/PM. Use formats, function or hard coding.

Left join table two with one on DOW.

Test if AM/PM is the same, assign Compliance.

[Ksharp]

If I understood what you mean .

data schedule ;
input (ID    Mon  Tue  Wed Thur Fri Sat sun     ) ($);
cards;
A1.   1.        1.       1.     2.     1.   1.   1
A2.   1.        3.        1.    1.     1.   1.    1
A3.   1.         2.       1.   1.      1.    1.    1
;
run;
data Actual     ;
input (ID   Date           Time     AM_PM)  (: $20.) ;
cards;
A1.   08/25/14      10:00        AM
A2.    08/25/14.     13:00       PM
A3.    08/25/14.       14:00     PM
;
run;

data temp(drop=i);
 set schedule;
 array x{*} $ mon--sun;
 do i=1 to dim(x);
  if x{i}='2.' then x{i}='AM';
   else if x{i}='3.' then x{i}='PM';
 end;
run;
data want(drop=i mon--sun );
 merge      Actual      temp;
 by id;
 array x{*} $ mon--sun;
 Compliance='NO ';
 do i=1 to dim(x);
 if  x{i}=AM_PM then Compliance='YES';
 end;
run;


 

Xia Keshan

[BETO]

Hi

KSharp,

is is there a way we get only what was schedule and compare it to what was actual?

for example have the table schedule but only use the date of week  that is in the actual table

sschedule

S M T  W TH F S

ACTUAL table

Id.   Date        Time.     Am pm

a1.   08/26/14.  10:00       am

since the date was on a Tuesday do my comparison only for that date?

is there a way I can only use the date of week schedule  an compare it to actual date process?

fyi schedule table has over 7k records  compare to a smaller sample from actual. So when I merge it show s all the 7k records is there a way only to show records for the date in actual ? Thanks again for your help

[Ksharp · Accepted solution]

I don't understand what you mean .

sschedule

S M T  W TH F S

What does this mean ?

"is there a way only to show records for the date in actual ? "

Yes. I would like to use Hash Table.

[BETO]

Thanks Ksharp