topic Re: Multiple row by ID to one row by ID in SAS Programming

Multiple row by ID to one row by ID

Nrjn7 — Tue, 11 May 2021 20:13:54 GMT

ID	Event	Score1	Score2	Score3	Score5	Score5
1	A	1	2
1	B			3	3
1	C					4
2	A	3	4
2	B			5	6
2	C					8
3	A	3	4

I'm working with a much bigger file bu his is the general flow.

How do I transform this to one row by ID?

Please suggest.

Re: Multiple row by ID to one row by ID

ballardw — Tue, 11 May 2021 21:12:46 GMT

You need to show exactly what you expect the result to look like. The value of EVENT is going to play a role somewhere but you have not described what that role would be. You may have to address exactly how many possible values of Event there are in the entire data set when specifying your rules.

Re: Multiple row by ID to one row by ID

Nrjn7 — Tue, 11 May 2021 21:16:27 GMT

Thanks for you reply.

What I'm looking for is something like this

ID	Score1	Score2	Score3	Score4	Score5
1	1	2	3	3	4
2	3	4	5	6	6
3	3	4	.	.	.

I'm having hard time understanding how it works and how do I account for "Event" variable.

Re: Multiple row by ID to one row by ID

novinosrin — Tue, 11 May 2021 21:26:55 GMT

data have;
input ID	Event $	Score1	Score2	Score3	Score4	Score5;
cards;
1	A	1	2	. . . 	 	 
1	B	. 	. 	3	3	. 
1	C	. . . . 	 	 	 	4
2	A	3	4	. . . 	 	 
2	B	. . 	 	5	6	. 
2	C	. . . . 	 	 	 	8
3	A	3	4	. . . 	 	 
;


data want;
 update have(obs=0) have;
 by id;
 drop event;
run;
/*OR*/
proc summary data=have nway;
 class id;
 var score:;
 output out=want(drop=_:) max=;
run;

Hi @Nrjn7 Please use one of the above. 1st one though needs your dataset to be sorted by ID whereas 2nd one doesn't