topic Re: Grouping Transpose Columns in SAS Programming

Grouping Transpose Columns

sarahzhou — Mon, 13 Jun 2022 10:07:12 GMT

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.

Re: Grouping Transpose Columns

tarheel13 — Mon, 13 Jun 2022 11:21:26 GMT

how are the groups supposed to be assigned?

Re: Grouping Transpose Columns

sarahzhou — Mon, 13 Jun 2022 14:37:37 GMT

I've changed EVENT_TX back by using case and followed by Tom's method. It worked.

Re: Grouping Transpose Columns

mkeintz — Mon, 13 Jun 2022 19:35:56 GMT

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

Re: Grouping Transpose Columns

sarahzhou — Tue, 28 Jun 2022 14:18:38 GMT

the solution is in Tom's reply, @Tom , thank you

https://communities.sas.com/t5/SAS-Programming/Transpose-column-with-chronological-order/m-p/817722#M322761