Hi
I have an input table:
TEAM_1 | TEAM_2 | EVENT_NM | EVENT_DT |
ALICE | JIMMY | Orange | 01-01-22 |
CINDY | BOB | Orange | 02-01-22 |
CINDY | BOB | Berry | 03-01-22 |
CINDY | BOB | C | 04-01-22 |
CINDY | BOB | Berry | 05-01-22 |
CINDY | BOB | Berry | 06-01-22 |
CINDY | BOB | C | 07-01-22 |
ELLA | JACK | Berry | 08-01-22 |
ELLA | JACK | D | 09-01-22 |
AMBER | DANIEL | E | 10-01-22 |
KITTY | ARRON | F | 11-01-22 |
I want the output like this:
TEAM_1 | TEAM_2 | group | Orange | Berry | C | D | E | F |
ALICE | JIMMY | 1 | 01-01-22 | . | . | . | . | . |
CINDY | BOB | 1 | 02-01-22 | 03-01-22 | 04-01-22 | . | . | . |
CINDY | BOB | 2 | . | 05-01-22 | . | . | . | . |
CINDY | BOB | 3 | . | 06-01-22 | 07-01-22 | . | . | . |
ELLA | JACK | 1 | . | 08-01-22 | . | 09-01-22 | . | . |
AMBER | DANIEL | 1 | . | . | . | . | 10-01-22 | . |
KITTY | ARRON | 1 | . | . | . | . | . | 11-01-22 |
Previouly Tom has help this, maybe he is too busy to see my new problem. Thx Tom.
data have; input TEAM_1 $ TEAM_2 $ EVENT_NM $ EVENT_DT :mmddyy. ; format event_dt yymmdd10.; cards; ALICE JIMMY Orange 1-1-2022 CINDY BOB Orange 1-2-2022 CINDY BOB Berry 1-3-2022 CINDY BOB C 1-4-2022 CINDY BOB Berry 1-5-2022 CINDY BOB Berry 1-6-2022 CINDY BOB C 1-7-2022 ELLA JACK Berry 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;
The code gives an wrong out put:
group + (event_nm <= lag(event_nm) )
this tric does not work anymore.
Appriciate your help.
how are the groups supposed to be assigned?
I've changed EVENT_TX back by using case and followed by Tom's method. It worked.
@sarahzhou wrote:
I've changed EVENT_TX back by using case and followed by Tom's method. It worked.
Then mark your statement, with a reference link to Tom's method, as the solution. Then readers know a solution has been found. And your topic will be more helpful to others.
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.