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

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_NOClinic_NODAYTIME_STARTTIME_STOP
12346789FRI01JUN1988:09:00:0001JUN1988:17:00:00
12346789MON01JUN1988:09:00:0001JUN1988:17:00:00
12346789THU01JUN1988:09:00:0001JUN1988:17:00:00
12346789TUE01JUN1988:09:00:0001JUN1988:17:00:00
12346789WED01JUN1988:09:00:0001JUN1988:17:00:00
21416530FRI01JUN1988:09:00:0001JUN1988:18:00:00
21416530MON01JUN1988:09:00:0001JUN1988:12:00:00
21416530SAT01JUN1988:09:00:0001JUN1988:15:00:00
21416530SUN01JUN1988:09:00:0001JUN1988:17:00:00
21416530THU01JUN1988:09:00:0001JUN1988:18:00:00
21416530TUE01JUN1988:09:00:0001JUN1988:18:00:00
21416530WED01JUN1988:09:00:0001JUN1988:18:00:00
21416530MON01JUN1988:13:30:0001JUN1988:18:00:00
32222077TUE01JUN1988:09:00:0001FEB2002:17:00:00
32222077WED01JUN1988:09:00:0001FEB2002:17:00:00
32223467FRI01JUN1988:09:00:0001JUN1988:19:00:00
32223467MON01JUN1988:09:00:0001JUN1988:19:00:00
32223467THU01JUN1988:09:00:0001JUN1988:19:00:00
32223467TUE01JUN1988:09:00:0001JUN1988:19:00:00
32224567FRI01JUN1988:09:00:0001JUN1988:19:30:00
32224567MON01JUN1988:09:00:0001JUN1988:19:30:00
32224567THU01JUN1988:09:00:0001JUN1988:19:30:00
32224567TUE01JUN1988:09:00:0001JUN1988:19:30:00
32224567WED01JUN1988:09:00:0001JUN1988:19:30:00

 

This is what I would like to get (Note Mon1 and Mon2, this one clinic takes a break)

 

Hospital_NOClinic_NOSUNMON1MON2TUESWEDTHUFRISAT
32222077   9:00 AM - 5:00 PM9:00 AM - 5:00 PM   
32224567 9:00 AM - 7:30 PM 9:00 AM - 7:30 PM9:00 AM - 7:30 PM9:00 AM - 7:30 PM9:00 AM - 7:30 PM 
32223467 9:00 AM - 7:00 PM 9:00 AM - 7:00 PM 9:00 AM - 7:00 PM9:00 AM - 7:00 PM 
12346789 9:00 AM - 5:00 PM 9:00 AM - 5:00 PM9:00 AM - 5:00 PM9:00 AM - 5:00 PM9:00 AM - 5:00 PM 
214165309:00 AM - 5:00 PM9:00 AM - 12:00 PM1:30:00 PM - 6:00 PM9:00 AM - 6:00 PM9:00 AM - 6:00 PM9:00 AM - 6:00 PM9:00 AM - 6:00 PM9: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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sas_student1
Quartz | Level 8

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. 

View solution in original post

1 REPLY 1
sas_student1
Quartz | Level 8

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 420 views
  • 0 likes
  • 1 in conversation