topic Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable in SAS Programming

Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

lh50 — Mon, 18 May 2020 14:45:52 GMT

I have a dataset where each participant received three tests, and the results of each test are in individual rows such that there are three rows for each participant. I want to collapse the rows so that each participant is represented by only one row. The data looks like this:

ID	Test1	Test2	Test3
V04001			0
V04001		1
V04001	0
V04002			0
V04002		0
V04002	0
V04003			0
V04003		1
V04003	1

And this is what I want:

ID	Test1	Test2	Test3
V04001	0	1	0
V04002	0	0	0
V04003	1	1	0

Most of the searching I've done for a solution ends up with PROC TRANSPOSE, but I can't seem to figure out how to make that work with what I need. Any help would be much appreciated!

Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

novinosrin — Mon, 18 May 2020 14:51:06 GMT

data have;
input ID $	Test1	Test2	Test3;
cards;
V04001	 .	. 	0
V04001	 .	1	 .
V04001	0	 .	. 
V04002	. .	 	0
V04002	 .	0	. 
V04002	0	. .	 
V04003	 .	. 	0
V04003	 .	1	. 
V04003	1	. .	 
;


data want;
 update have(obs=0) have;
 by id;
run;

Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

ed_sas_member — Mon, 18 May 2020 14:52:17 GMT

Hi @lh50

Please try this

proc sql;
	create table want as
	select id, sum(test1) as test1,
		   sum(test2) as test2,
		   sum(test3) as test3
	from have
	group by id;
quit;

Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

ballardw — Mon, 18 May 2020 14:53:39 GMT

If your TEST variables are all numeric then an approach with proc summary/means and the MAX function may be appropriate:

Proc summary data = have nway;
   class id;
   var test1 test2 test3;
   output out=want (drop=_type_ _freq_) sum =;
run;

If the values are not numeric this will not work as VAR variables in summary must be numeric.

Other considerations arise if there are other variables in your data as well. Which values should be kept in the "collapsing" process would need to be specified to provide a different solution as the above will remove any other variables.

Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

s_lassen — Mon, 18 May 2020 14:56:02 GMT

That is very easy, using the UPDATE statement:

data have;
 input ID $ test1-test3;
cards;
V04001 . . 0
V04001 . 1 . 
V04001 0 . . 
V04002 . . 0
V04002 . 0 . 
V04002 0 . . 
V04003 . . 0
V04003 . 1 . 
V04003 1 . .
;run;

data want;
  update have(obs=0) have;
  by id;
run;

Re: Collapse multiple rows into one by unique ID, each row corresponds to value of 1 variable

lh50 — Mon, 18 May 2020 14:59:30 GMT

This worked perfectly. Thank you!