Transpose column with chronological order

[sarahzhou]

Hi,

I have a table

TEAM _1	TEAM _2	EVENT_NM	EVENT_DT
ALICE	JIMMY	A	1-1-2022
CINDY	BOB	A	1-2-2022
CINDY	BOB	B	1-3-2022
CINDY	BOB	C	1-4-2022
CINDY	BOB	B	1-5-2022
CINDY	BOB	B	1-6-2022
CINDY	BOB	C	1-7-2022
ELLA	JACK	B	1-8-2022
ELLA	JACK	D	1-9-2022
AMBER	DANIEL	E	1-10-2022
KITTY	ARRON	F	1-11-2022

And I want to have its transpose columns with the chronological order by each EVENT_NM,

TEAM _1	TEAM _2	A	B	C	D	E	F
ALICE	JIMMY	1-1-2022
CINDY	BOB	1-2-2022	1-3-2022	1-4-2022
CINDY	BOB		1-5-2022	1-7-2022
CINDY	BOB		1-6-2022
ELLA	JACK		1-8-2022		1-9-2022
ELLA	JACK
AMBER	DANIEL					1-10-2022
KITTY	ARRON						1-11-2022

How do I do that in data step?

Kindly help.

Thanks

[sarahzhou]

Sorry there is a typo in my post,

the original table is 

TEAM A	TEAM B	EVENT_NM	EVENT_DT
ALICE	JIMMY	A	1-1-2022
CINDY	BOB	A	1-2-2022
CINDY	BOB	B	1-3-2022
CINDY	BOB	C	1-4-2022
CINDY	BOB	B	1-5-2022
CINDY	BOB	B	1-6-2022
CINDY	BOB	C	1-7-2022
ELLA	JACK	B	1-8-2022
ELLA	JACK	D	1-9-2022
AMBER	DANIEL	E	1-10-2022
KITTY	ARRON	F	1-11-2022

after transpose should look like below

TEAM A	TEAM B	A	B	C	D	E	F
ALICE	JIMMY	1-1-2022
CINDY	BOB	1-2-2022	1-3-2022	1-4-2022
CINDY	BOB		1-5-2022	1-7-2022
CINDY	BOB		1-6-2022
ELLA	JACK		1-8-2022		1-9-2022
AMBER	DANIEL					1-10-2022
KITTY	ARRON						1-11-2022

[yabwon]

Try this:

data have;
input TEAM_A $ TEAM_B $ EVENT_NM $ EVENT_DT ddmmyy10.;
format EVENT_DT ddmmyy10.;
cards;
ALICE	JIMMY	A	1-1-2022
CINDY	BOB	A	1-2-2022
CINDY	BOB	B	1-3-2022
CINDY	BOB	C	1-4-2022
CINDY	BOB	B	1-5-2022
CINDY	BOB	B	1-6-2022
CINDY	BOB	C	1-7-2022
ELLA	JACK	B	1-8-2022
ELLA	JACK	D	1-9-2022
AMBER	DANIEL	E	1-10-2022
KITTY	ARRON	F	1-11-2022
;
run;

proc sql;
  create table temp as
  select distinct
  EVENT_NM
  from have
  order by EVENT_NM
  ;
quit;

proc sort data = have;
  by TEAM_A TEAM_B EVENT_NM EVENT_DT;
run;
data have2;
  set have;
  by TEAM_A TEAM_B EVENT_NM EVENT_DT; 
  if first.EVENT_NM then x = 1;
                    else x + 1;
run;
proc sort data = have2;
  by TEAM_A TEAM_B x EVENT_NM EVENT_DT;
run;

data have_view / view = have_view;
  if 0 then set have2;
  set temp have2;
run;

proc transpose data = have_view out = want(drop=_: x);
  by TEAM_A TEAM_B x;
  id EVENT_NM;
  var EVENT_DT;
run;

data want;
  set want;
    ord = min(of _numeric_);
run;
proc sort data = want out = want(drop=ord);
  by ord TEAM_A TEAM_B;
run;

Bart

[Tom · Accepted solution]

So if you want to preserve the existing order and use PROC TRANSPOSE you need to create and extra variable to indicate when to start a new observation for the same values of the TEAM* variables.

Looks like you want to start a new line/group when the EVENT_NM stops increasing (like with the CINDY/BOB records).

data have;
  input TEAM_1 $ TEAM_2 $ EVENT_NM $ EVENT_DT :mmddyy. ;
  format event_dt yymmdd10.;
cards;
ALICE  JIMMY  A  1-1-2022
CINDY  BOB  A  1-2-2022
CINDY  BOB  B  1-3-2022
CINDY  BOB  C  1-4-2022
CINDY  BOB  B  1-5-2022
CINDY  BOB  B  1-6-2022
CINDY  BOB  C  1-7-2022
ELLA  JACK  B  1-8-2022
ELLA  JACK  D  1-9-2022
AMBER  DANIEL  E  1-10-2022
KITTY  ARRON  F  1-11-2022
;

data groups;
  set have;
  by team_1 team_2 notsorted ;
  group + (event_nm <= lag(event_nm) );
  if first.team_2 then group=1;
run;
proc transpose data=groups out=wide(drop=_name_);
  by team_1 team_2 group notsorted;
  id event_nm;
  var event_dt;
run;
proc print data=wide;
run;

[sarahzhou]

Thank you!

[sarahzhou]

Hi, 

If there is an EVENT_ID that needs to display next to the EVENT_DT,

How do I modified the code?

The input table is now:

TEAM A	TEAM B	EVENT_NM	EVENT_DT	EVENT_ID
ALICE	JIMMY	A	1-1-2022	A1
CINDY	BOB	A	1-2-2022	A2
CINDY	BOB	B	1-3-2022	B1
CINDY	BOB	C	1-4-2022	C1
CINDY	BOB	B	1-5-2022	B2
CINDY	BOB	B	1-6-2022	B3
CINDY	BOB	C	1-7-2022	C2
ELLA	JACK	B	1-8-2022	B4
ELLA	JACK	D	1-9-2022	D1
AMBER	DANIEL	E	1-10-2022	E1
KITTY	ARRON	F	1-11-2022	F1

The expected out put should look like this:

TEAM_1	TEAM_2	A_DT	A_ID	B_DT	B_ID	C_DT	C_ID	D_DT	D_ID	E_DT	E_ID	F_DT	F_ID
ALICE	JIMMY	01-01-22	A1	.		.		.		.		.
CINDY	BOB	02-01-22	A2	03-01-22	B1	04-01-22	C1	.		.		.
CINDY	BOB	.		05-01-22	B2	.		.		.		.
CINDY	BOB	.		06-01-22	B3	07-01-22	C2	.		.		.
ELLA	JACK	.		08-01-22	B4	.		09-01-22	D1	.		.
AMBER	DANIEL	.		.		.		.		10-01-22	E1	.
KITTY	ARRON	.		.		.		.		.		11-01-22	F1

I used the full outer join and it does not correct T.T

data have;
  input TEAM_1 $ TEAM_2 $ EVENT_NM $ EVENT_DT :mmddyy. EVENT_ID $ ;
  format event_dt yymmdd10.;
cards;
ALICE  JIMMY  A  1-1-2022 A1
CINDY  BOB  A  1-2-2022 A2
CINDY  BOB  B  1-3-2022 B1
CINDY  BOB  C  1-4-2022 C1
CINDY  BOB  B  1-5-2022 B2
CINDY  BOB  B  1-6-2022 B3
CINDY  BOB  C  1-7-2022 C2
ELLA  JACK  B  1-8-2022 B4
ELLA  JACK  D  1-9-2022 D1 
AMBER  DANIEL  E  1-10-2022 E1
KITTY  ARRON  F  1-11-2022 F1
;

data groups1;
  set have;
  by team_1 team_2 notsorted ;
  group + (EVENT_NM <= lag(EVENT_NM) );
  if first.team_2 then group=1;
run;
proc transpose data=groups1 out=wide1(DROP=_NAME_);
  by team_1 team_2  group notsorted;
  id  EVENT_NM ;
  var EVENT_ID;
run;
proc print data=wide1;
run;

data groups2;
  set have;
  by team_1 team_2 notsorted ;
  group + (EVENT_NM <= lag(EVENT_NM) );
  if first.team_2 then group=1;
run;
proc transpose data=groups2 out=wide2(DROP=_NAME_);
  by team_1 team_2  group notsorted;
  id  EVENT_NM;
  var EVENT_dt;
run;
proc print data=wide2;
run;

PROC SQL;
CREATE TABLE EVENT_DT AS
SELECT 
TEAM_1,
TEAM_2,
A AS A_DT,
B AS B_DT,
C AS C_DT,
D AS D_DT,
E AS E_DT,
F AS F_DT
FROM WIDE2;
QUIT;

PROC SQL;
CREATE TABLE EVENT_ID AS
SELECT 
TEAM_1,
TEAM_2,
A AS A_ID,
B AS B_ID,
C AS C_ID,
D AS D_ID,
E AS E_ID,
F AS F_ID
FROM WIDE1;
QUIT;
 
PROC SQL;
CREATE TABLE ID_DT AS
SELECT 
A.TEAM_1,
A.TEAM_2,

B.A_DT,
A.A_ID,

B.B_DT,
A.B_ID,

B.C_DT,
A.C_ID,

B.D_DT,
A.D_ID,

B.E_DT,
A.E_ID,

B.F_DT,
A.F_ID
 

FROM EVENT_ID A FULL OUTER JOIN EVENT_DT B
ON A.TEAM_1=B.TEAM_1 AND A.TEAM_2 = B.TEAM_2;
QUIT;

Appreciate your help.

[sarahzhou]

I added "group" and solve the issue.

data have;
  input TEAM_1 $ TEAM_2 $ EVENT_NM $ EVENT_DT :mmddyy. EVENT_ID $ ;
  format event_dt yymmdd10.;
cards;
ALICE  JIMMY  A  1-1-2022 A1
CINDY  BOB  A  1-2-2022 A2
CINDY  BOB  B  1-3-2022 B1
CINDY  BOB  C  1-4-2022 C1
CINDY  BOB  B  1-5-2022 B2
CINDY  BOB  B  1-6-2022 B3
CINDY  BOB  C  1-7-2022 C2
ELLA  JACK  B  1-8-2022 B4
ELLA  JACK  D  1-9-2022 D1 
AMBER  DANIEL  E  1-10-2022 E1
KITTY  ARRON  F  1-11-2022 F1
;

data groups1;
  set have;
  by team_1 team_2 notsorted ;
  group + (EVENT_NM <= lag(EVENT_NM) );
  if first.team_2 then group=1;
run;
proc transpose data=groups1 out=wide1(DROP=_NAME_);
  by team_1 team_2 group group notsorted;
  id  EVENT_NM ;
  var EVENT_ID;
run;
proc print data=wide1;
run;

data groups2;
  set have;
  by team_1 team_2 notsorted ;
  group + (EVENT_NM <= lag(EVENT_NM) );
  if first.team_2 then group=1;
run;
proc transpose data=groups2 out=wide2(DROP=_NAME_);
  by team_1 team_2 group group notsorted;
  id  EVENT_NM;
  var EVENT_dt;
run;
proc print data=wide2;
run;

PROC SQL;
CREATE TABLE EVENT_DT AS
SELECT 
TEAM_1,
TEAM_2,
GROUP,
A AS A_DT,
B AS B_DT,
C AS C_DT,
D AS D_DT,
E AS E_DT,
F AS F_DT
FROM WIDE2;
QUIT;

PROC SQL;
CREATE TABLE EVENT_ID AS
SELECT 
TEAM_1,
TEAM_2,
GROUP,
A AS A_ID,
B AS B_ID,
C AS C_ID,
D AS D_ID,
E AS E_ID,
F AS F_ID
FROM WIDE1;
QUIT;
 
PROC SQL;
CREATE TABLE ID_DT AS
SELECT 
A.TEAM_1,
A.TEAM_2,

B.A_DT,
A.A_ID,

B.B_DT,
A.B_ID,

B.C_DT,
A.C_ID,

B.D_DT,
A.D_ID,

B.E_DT,
A.E_ID,

B.F_DT,
A.F_ID
 

FROM EVENT_ID A FULL OUTER JOIN EVENT_DT B
ON A.TEAM_1=B.TEAM_1 AND A.TEAM_2 = B.TEAM_2 and A.GROUP=B.GROUP;
QUIT;

Is there a better way ? Mine is too lengthy.

Appcretiate your help.

[Tom]

You could just transpose the two variables into separate datasets and then merge them back together.

If you need to keep that original order then make a new variable that keeps that overall order.

Let's call it ROW.

data groups1;
  set have;
  by team_1 team_2 notsorted ;
  row + (EVENT_NM <= lag(EVENT_NM) ) or first.team_2;
  group + (EVENT_NM <= lag(EVENT_NM) );
  if first.team_2 then group=1;
run;
proc transpose data=groups1 out=wide1(DROP=_NAME_) suffix=_nm;
  by row team_1 team_2 group;
  id  EVENT_NM ;
  var EVENT_ID;
run;
proc transpose data=groups1 out=wide2(DROP=_NAME_) suffix=_dt;
  by row team_1 team_2 group;
  id  EVENT_NM ;
  var EVENT_dt;
run;
data wide;
 merge wide1 wide2 ;
  by row team_1 team_2 group;
run;
proc print;
run;

[sarahzhou]

Hi,  How can I get the group number is the EVENT_NM is not in albert order, eg: "fancy","ordinary","casual"  etc... Thanks