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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.