BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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.

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Ksharp
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Thanks Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 850 views
  • 0 likes
  • 3 in conversation