Accepted Solutions
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;
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;
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.
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;
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
703 views
-
0 likes
-
-
