Hello,
I hope the folks here can assist:
I have a dataset with provider hours of operation. I would like to transpose the database so that the day of the week is a column with the time as the value for each provider and program by row. I have a sample of what I have the dataset look like right now and what I would like it to look like (there is SAS code too to create the dataset). You will note that for some of the providers the day of the week duplicates (there are two Mondays, two Thursdays and two Fridays by provider and program), in that case I don't mind having another columns for each (see second table of what the table can look like).
Suggestions on how to achieve this, please.
Thanks!
Dataset looks like this:
Prov | Prog | Day | Time |
24 | 166 | FRI | 10:00AM-5:00PM |
24 | 166 | MON | 10:00AM-8:00PM |
24 | 166 | THU | 10:00AM-12:30PM |
24 | 166 | THU | 1:00PM-9:00PM |
24 | 166 | WED | 10:00AM-9:30PM |
28 | 445 | FRI | 10:00AM-5:00PM |
28 | 445 | MON | 10:00AM-9:30PM |
28 | 445 | SAT | 10:00AM-2:00PM |
28 | 445 | THU | 10:00AM-9:30PM |
28 | 445 | TUE | 10:00AM-9:30PM |
28 | 445 | WED | 10:00AM-9:30PM |
42 | 2356 | FRI | 8:30AM-4:30PM |
42 | 2356 | MON | 8:30AM-4:30PM |
42 | 2356 | THU | 8:30AM-4:30PM |
42 | 2356 | TUE | 8:30AM-4:30PM |
42 | 2356 | WED | 8:30AM-4:30PM |
42 | 6345 | FRI | 8:30AM-12:30PM |
42 | 6345 | FRI | 1:30PM-4:30PM |
42 | 6345 | THU | 8:30AM-7:00PM |
42 | 6345 | TUE | 8:30AM-7:00PM |
42 | 6345 | WED | 8:30AM-4:30PM |
42 | 9856 | FRI | 8:30AM-4:30PM |
42 | 9856 | MON | 8:30AM-12:30PM |
42 | 9856 | MON | 1:30PM-4:30PM |
42 | 9856 | TUE | 8:30AM-4:30PM |
42 | 9856 | WED | 8:30AM-4:30PM |
Want it to look like this:
Prov | Prog | MON | MON2 | TUE | WED | THU | THU2 | FRI | FRI2 | SAT |
24 | 166 | 10:00AM-8:00PM | 10:00AM-9:30PM | 10:00AM-12:30PM | 1:00PM-9:00PM | 10:00AM-5:00PM | ||||
28 | 445 | 10:00AM-9:30PM | 10:00AM-9:30PM | 10:00AM-9:30PM | 10:00AM-9:30PM | 10:00AM-5:00PM | 10:00AM-2:00PM | |||
42 | 2356 | 8:30AM-4:30PM | 8:30AM-4:30PM | 8:30AM-4:30PM | 8:30AM-4:30PM | 8:30AM-4:30PM | ||||
42 | 6345 | 8:30AM-7:00PM | 8:30AM-4:30PM | 8:30AM-7:00PM | 8:30AM-12:30PM | 1:30PM-4:30PM | ||||
42 | 9856 | 8:30AM-12:30PM | 1:30PM-4:30PM | 8:30AM-4:30PM | 8:30AM-4:30PM | 8:30AM-4:30PM |
data test; input Prov:$5. PROG :$5. Day :$3. Time: $21.; cards; 24 166 FRI 10:00AM-5:00PM 24 166 MON 10:00AM-8:00PM 24 166 THU 10:00AM-12:30PM 24 166 THU 1:00PM-9:00PM 24 166 WED 10:00AM-9:30PM 28 445 FRI 10:00AM-5:00PM 28 445 MON 10:00AM-9:30PM 28 445 SAT 10:00AM-2:00PM 28 445 THU 10:00AM-9:30PM 28 445 TUE 10:00AM-9:30PM 28 445 WED 10:00AM-9:30PM 42 2356 FRI 8:30AM-4:30PM 42 2356 MON 8:30AM-4:30PM 42 2356 THU 8:30AM-4:30PM 42 2356 TUE 8:30AM-4:30PM 42 2356 WED 8:30AM-4:30PM 42 6345 FRI 8:30AM-12:30PM 42 6345 FRI 1:30PM-4:30PM 42 6345 THU 8:30AM-7:00PM 42 6345 TUE 8:30AM-7:00PM 42 6345 WED 8:30AM-4:30PM 42 9856 FRI 8:30AM-4:30PM 42 9856 MON 8:30AM-12:30PM 42 9856 MON 1:30PM-4:30PM 42 9856 TUE 8:30AM-4:30PM 42 9856 WED 8:30AM-4:30PM ; run;
Here's where you need to add the variable:
proc sort data=test;
by prov prog day;
run;
data t2;
set test;
by prov prog day;
if first.day
then seq = 1;
else seq + 1;
d = input(day,inwd.);
lab = cats(day,seq);
run;
proc transpose data=t2 out=wide (drop=_name_);
by prov prog;
id d seq;
idlabel lab;
var time;
run;
proc sql;
create table vars as
select name, label
from dictionary.columns
where libname = "WORK" and memname = "WIDE" and upcase(name) not in ("PROV","PROG")
order by name
;
quit;
data _null_;
call execute("data want;");
call execute("retain Prov Prog");
do until (d1);
set vars end=d1;
call execute(" "!!name);
end;
call execute(";set wide;rename");
do until (d2);
set vars end=d2;
call execute(" "!!cats(name,"=",label));
end;
call execute(";run;");
stop;
run;
Untested, posted from my tablet.
It's a little complicated, but here is:
data test;
input Prov:$5. PROG :$5. Day :$3. Time: $21.;
cards;
24 166 FRI 10:00AM-5:00PM
24 166 MON 10:00AM-8:00PM
24 166 THU 10:00AM-12:30PM
24 166 THU 1:00PM-9:00PM
24 166 WED 10:00AM-9:30PM
28 445 FRI 10:00AM-5:00PM
28 445 MON 10:00AM-9:30PM
28 445 SAT 10:00AM-2:00PM
28 445 THU 10:00AM-9:30PM
28 445 TUE 10:00AM-9:30PM
28 445 WED 10:00AM-9:30PM
42 2356 FRI 8:30AM-4:30PM
42 2356 MON 8:30AM-4:30PM
42 2356 THU 8:30AM-4:30PM
42 2356 TUE 8:30AM-4:30PM
42 2356 WED 8:30AM-4:30PM
42 6345 FRI 8:30AM-12:30PM
42 6345 FRI 1:30PM-4:30PM
42 6345 THU 8:30AM-7:00PM
42 6345 TUE 8:30AM-7:00PM
42 6345 WED 8:30AM-4:30PM
42 9856 FRI 8:30AM-4:30PM
42 9856 MON 8:30AM-12:30PM
42 9856 MON 1:30PM-4:30PM
42 9856 TUE 8:30AM-4:30PM
42 9856 WED 8:30AM-4:30PM
;
proc sort data=test;
by prov day;
run;
proc format;
invalue inwd
"MON" = 1
"TUE" = 2
"WED" = 3
"THU" = 4
"FRI" = 5
"SAT" = 6
"SUN" = 7
;
value outwd
1 = "MON"
2 = "TUE"
3 = "WED"
4 = "THU"
5 = "FRI"
6 = "SAT"
7 = "SUN"
run;
data t2;
set test;
by prov day;
if first.day
then seq = 1;
else seq + 1;
d = input(day,inwd.);
lab = cats(day,seq);
run;
proc transpose data=t2 out=wide (drop=_name_);
by prov;
id d seq;
idlabel lab;
var time;
run;
proc sql;
create table vars as
select name, label
from dictionary.columns
where libname = "WORK" and memname = "WIDE" and upcase(name) ne "PROV"
order by name
;
quit;
data _null_;
call execute("data want;");
call execute("retain Prov");
do until (d1);
set vars end=d1;
call execute(" "!!name);
end;
call execute(";set wide;rename");
do until (d2);
set vars end=d2;
call execute(" "!!cats(name,"=",label));
end;
call execute(";run;");
stop;
run;
The last 2 steps are there to bring the columns in order.
@Kurt_Bremser fantastic!! Just what I need.
Almost there.
I need to do a "by" statement by both prov and prog. So in your code I added prog to the by statement.
However, I don't really understand how the last data _null_ is working to add the prog item too.
That is since I have an extract column for prog the data _null_ statement, which is great as I do need to order by the day, isn't working. Recommendation on how to add the prog item to that statement as well?
Thanks!
Here's where you need to add the variable:
proc sort data=test;
by prov prog day;
run;
data t2;
set test;
by prov prog day;
if first.day
then seq = 1;
else seq + 1;
d = input(day,inwd.);
lab = cats(day,seq);
run;
proc transpose data=t2 out=wide (drop=_name_);
by prov prog;
id d seq;
idlabel lab;
var time;
run;
proc sql;
create table vars as
select name, label
from dictionary.columns
where libname = "WORK" and memname = "WIDE" and upcase(name) not in ("PROV","PROG")
order by name
;
quit;
data _null_;
call execute("data want;");
call execute("retain Prov Prog");
do until (d1);
set vars end=d1;
call execute(" "!!name);
end;
call execute(";set wide;rename");
do until (d2);
set vars end=d2;
call execute(" "!!cats(name,"=",label));
end;
call execute(";run;");
stop;
run;
Untested, posted from my tablet.
@Kurt_Bremser fantastic thanks!
I was playing around on adding prog was close but no cigar!
Thank you!
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.