How to transpose from long to wide with duplicate values

[sas_student1]

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;

[Kurt_Bremser]

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.

[sas_student1]

@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!

[Kurt_Bremser · Accepted solution]

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.

[sas_student1]

@Kurt_Bremser  fantastic thanks!

I was playing around on adding prog was close but no cigar!

Thank you!