Make major adjustment to dataset - how to?

Mikkel_madsen — Tue, 28 Apr 2020 07:58:08 GMT

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!

Re: Make major adjustment to dataset - how to?

Jagadishkatam — Tue, 28 Apr 2020 08:08:37 GMT

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;

topic Re: Make major adjustment to dataset - how to? in SAS Programming

Make major adjustment to dataset - how to?

Re: Make major adjustment to dataset - how to?