Make major adjustment to dataset - how to?

[Mikkel_madsen]

Hello everyone,

I have a dataset (openinghours.data) that I want to adjust. It looks like:

ID          Type                   Start_time       End_time

102        Monday              8:00:00            17:00:00

102        Tuesday             8:00:00            17:00:00

102        Wednesday        8:00:00            17:00:00

102        Thursday            8:00:00            17:00:00

102        Friday                 8:00:00            17:00:00

104        Monday              7:00:00            19:00:00

104        Tuesday             7:00:00            19:00:00

104        Wednesday        7:00:00            19:00:00

104        Thursday            7:00:00            19:00:00

104        Friday                 7:00:00            19:00:00

111        Monday              9:00:00            21:00:00

111        Tuesday             9:00:00            21:00:00

111        Wednesday        9:00:00            21:00:00

111        Thursday            9:00:00            21:00:00

111        Friday                 9:00:00            21:00:00

111        Saturday            10:00:00          15:00:00

111        Sunday              10:00:00          13:00:00

Is it possible to re-arrange the dataset to something that only have one observation per ID number? It should still be possible to use the Start_time and End_time for each observation. Maybe something like this:

ID       Monday                     Tuesday                 Wednesday            ....... etc   

102    8:00:00-17:00:00       8:00:00-17:00:00    8:00:00-17:00:00   ....... etc

Later on, I have another dataset with the same ID numbers and some observation dates (e.g. 17FEB2018). I then want to ask, which observations from this dataset (openinghours.data) that are available on a given date and a given time (in the opening hours between Start_time and End_time). Is that possible with the setup just described?

I'm using SAS Enterprise Guide Version 9.1 and I am all new to this! 

Thank you in advance! 

[Jagadishkatam]

We can get the expected output as per the first dataset, but with regard to merging the first dataset with second is complicated as there is no date in the first dataset as there is only time.

data have;
input ID Type $ Start_time:time8. End_time:time8.;
time=catx('-',put(Start_time,time8.),put(End_time,time8.));
cards;
102 Monday 8:00:00 17:00:00
102 Tuesday 8:00:00 17:00:00
102 Wednesday 8:00:00 17:00:00
102 Thursday 8:00:00 17:00:00
102 Friday 8:00:00 17:00:00
104 Monday 7:00:00 19:00:00
104 Tuesday 7:00:00 19:00:00
104 Wednesday 7:00:00 19:00:00
104 Thursday 7:00:00 19:00:00
104 Friday 7:00:00 19:00:00
111 Monday 9:00:00 21:00:00
111 Tuesday 9:00:00 21:00:00
111 Wednesday 9:00:00 21:00:00
111 Thursday 9:00:00 21:00:00
111 Friday 9:00:00 21:00:00
111 Saturday 10:00:00 15:00:00
111 Sunday 10:00:00 13:00:00
;


proc transpose data=have out=trans;
by id;
id type;
var time;
run;