Hello,
I have as SAS dataset that has hours of operation by days for clinics within hospitals. The dataset has time start and time stop as two separate columns for each clinic by hospital by day of the week. What I would like to do is transpose the data so the each row is a unique combination of hospital and clinic, the day of the week is transposed to sperate columns and the start and stop time is in one cell concatenated. Further more the time start and stop are datetime20. formatted where the time is displayed as 24hrs, I would like to extract just the time and display it as AMPM.
So this is what the table I have looks like:
Hospital_NO | Clinic_NO | DAY | TIME_START | TIME_STOP |
1234 | 6789 | FRI | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
1234 | 6789 | MON | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
1234 | 6789 | THU | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
1234 | 6789 | TUE | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
1234 | 6789 | WED | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
2141 | 6530 | FRI | 01JUN1988:09:00:00 | 01JUN1988:18:00:00 |
2141 | 6530 | MON | 01JUN1988:09:00:00 | 01JUN1988:12:00:00 |
2141 | 6530 | SAT | 01JUN1988:09:00:00 | 01JUN1988:15:00:00 |
2141 | 6530 | SUN | 01JUN1988:09:00:00 | 01JUN1988:17:00:00 |
2141 | 6530 | THU | 01JUN1988:09:00:00 | 01JUN1988:18:00:00 |
2141 | 6530 | TUE | 01JUN1988:09:00:00 | 01JUN1988:18:00:00 |
2141 | 6530 | WED | 01JUN1988:09:00:00 | 01JUN1988:18:00:00 |
2141 | 6530 | MON | 01JUN1988:13:30:00 | 01JUN1988:18:00:00 |
3222 | 2077 | TUE | 01JUN1988:09:00:00 | 01FEB2002:17:00:00 |
3222 | 2077 | WED | 01JUN1988:09:00:00 | 01FEB2002:17:00:00 |
3222 | 3467 | FRI | 01JUN1988:09:00:00 | 01JUN1988:19:00:00 |
3222 | 3467 | MON | 01JUN1988:09:00:00 | 01JUN1988:19:00:00 |
3222 | 3467 | THU | 01JUN1988:09:00:00 | 01JUN1988:19:00:00 |
3222 | 3467 | TUE | 01JUN1988:09:00:00 | 01JUN1988:19:00:00 |
3222 | 4567 | FRI | 01JUN1988:09:00:00 | 01JUN1988:19:30:00 |
3222 | 4567 | MON | 01JUN1988:09:00:00 | 01JUN1988:19:30:00 |
3222 | 4567 | THU | 01JUN1988:09:00:00 | 01JUN1988:19:30:00 |
3222 | 4567 | TUE | 01JUN1988:09:00:00 | 01JUN1988:19:30:00 |
3222 | 4567 | WED | 01JUN1988:09:00:00 | 01JUN1988:19:30:00 |
This is what I would like to get (Note Mon1 and Mon2, this one clinic takes a break)
Hospital_NO | Clinic_NO | SUN | MON1 | MON2 | TUES | WED | THU | FRI | SAT |
3222 | 2077 | 9:00 AM - 5:00 PM | 9:00 AM - 5:00 PM | ||||||
3222 | 4567 | 9:00 AM - 7:30 PM | 9:00 AM - 7:30 PM | 9:00 AM - 7:30 PM | 9:00 AM - 7:30 PM | 9:00 AM - 7:30 PM | |||
3222 | 3467 | 9:00 AM - 7:00 PM | 9:00 AM - 7:00 PM | 9:00 AM - 7:00 PM | 9:00 AM - 7:00 PM | ||||
1234 | 6789 | 9:00 AM - 5:00 PM | 9:00 AM - 5:00 PM | 9:00 AM - 5:00 PM | 9:00 AM - 5:00 PM | 9:00 AM - 5:00 PM | |||
2141 | 6530 | 9:00 AM - 5:00 PM | 9:00 AM - 12:00 PM | 1:30:00 PM - 6:00 PM | 9:00 AM - 6:00 PM | 9:00 AM - 6:00 PM | 9:00 AM - 6:00 PM | 9:00 AM - 6:00 PM | 9:00 AM - 3:00 PM |
here is the sas code for the dataset example:
data have;
input Hospital_NO 5. Clinic_NO 5. DAY $3. TIME_START DATETIME20. TIME_STOP DATETIME20.;
format TIME_START DATETIME20. TIME_STOP DATETIME20.;
informat TIME_START DATETIME20. TIME_STOP DATETIME20.;
datalines;
1234 6789 FRI 01JUN1988:09:00:00 01JUN1988:17:00:00
1234 6789 MON 01JUN1988:09:00:00 01JUN1988:17:00:00
1234 6789 THU 01JUN1988:09:00:00 01JUN1988:17:00:00
1234 6789 TUE 01JUN1988:09:00:00 01JUN1988:17:00:00
1234 6789 WED 01JUN1988:09:00:00 01JUN1988:17:00:00
2141 6530 FRI 01JUN1988:09:00:00 01JUN1988:18:00:00
2141 6530 MON 01JUN1988:09:00:00 01JUN1988:12:00:00
2141 6530 SAT 01JUN1988:09:00:00 01JUN1988:15:00:00
2141 6530 SUN 01JUN1988:09:00:00 01JUN1988:17:00:00
2141 6530 THU 01JUN1988:09:00:00 01JUN1988:18:00:00
2141 6530 TUE 01JUN1988:09:00:00 01JUN1988:18:00:00
2141 6530 WED 01JUN1988:09:00:00 01JUN1988:18:00:00
2141 6530 MON 01JUN1988:13:30:00 01JUN1988:18:00:00
3222 2077 TUE 01JUN1988:09:00:00 01FEB2002:17:00:00
3222 2077 WED 01JUN1988:09:00:00 01FEB2002:17:00:00
3222 3467 FRI 01JUN1988:09:00:00 01JUN1988:19:00:00
3222 3467 MON 01JUN1988:09:00:00 01JUN1988:19:00:00
3222 3467 THU 01JUN1988:09:00:00 01JUN1988:19:00:00
3222 3467 TUE 01JUN1988:09:00:00 01JUN1988:19:00:00
3222 4567 FRI 01JUN1988:09:00:00 01JUN1988:19:30:00
3222 4567 MON 01JUN1988:09:00:00 01JUN1988:19:30:00
3222 4567 THU 01JUN1988:09:00:00 01JUN1988:19:30:00
3222 4567 TUE 01JUN1988:09:00:00 01JUN1988:19:30:00
3222 4567 WED 01JUN1988:09:00:00 01JUN1988:19:30:00
;
run;
Thank you!
Nevermind figured it out!
Used the below to create the combination for time:
time=strip(put((timepart(time_start)), timeampm7.))||'-'||strip(put((timepart(time_stop)), timeampm7.));
and then used the solution from "How to transpose from long to wide with duplicate values" post to complete the rest.
Nevermind figured it out!
Used the below to create the combination for time:
time=strip(put((timepart(time_start)), timeampm7.))||'-'||strip(put((timepart(time_stop)), timeampm7.));
and then used the solution from "How to transpose from long to wide with duplicate values" post to complete the rest.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.