BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lh50
Fluorite | Level 6

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:

 

IDTest1Test2Test3
V04001  0
V04001 1 
V040010  
V04002  0
V04002 0 
V040020  
V04003  0
V04003 1 
V040031  

 

And this is what I want:

IDTest1Test2Test3
V04001010
V04002000
V04003110

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20
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;
ed_sas_member
Meteorite | Level 14

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;
ballardw
Super User

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.

s_lassen
Meteorite | Level 14

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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 webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3754 views
  • 2 likes
  • 5 in conversation