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

Screen Shot 2020-11-15 at 10.40.04 PM.png

Dear All,

Could someone explain what is the best way to combine the rows with same USUBJID in the table above to make it look like the one below? I can do it using merge but I think there might be a better way. Thanks in advance for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Still another way (only for numeric variables):

proc sql;
create table want as
  select
    usubjid,
    max(weight) as weight,
    max(height) as height
  from have
  group by usubjid
;
quit;

 

View solution in original post

5 REPLIES 5
hhinohar
Quartz | Level 8
data have;
	infile datalines dlm="09"x;
	input usubjid $ weight height;
	datalines;
A1	100	.
A1	.	162
A2	150	
A2	.	190
;
run;

data want;
	set have;
	by usubjid;
	retain _weight;
	*retain nonmissing values;
	if ^missing(weight) then
		_weight=weight;
	*put it back to weight variable again;
	weight=_weight;
	
	*output only last obs;
	if last.usubjid;
	drop _weight;
run;
andreas_lds
Jade | Level 19

Are there always exactly two observations per usubjid? With exactly one missing data-value?

If that is the case, try:

data want;
   merge 
      have(keep= usubjid weight where=(not missing(weight)))
      have(keep= usubjid height where=(not missing(height)))
   ;
   by usubjid;
run;

@hhinohar : thanks for providing data in usable form.

hhinohar
Quartz | Level 8

You're welcome😀

Kurt_Bremser
Super User

Still another way (only for numeric variables):

proc sql;
create table want as
  select
    usubjid,
    max(weight) as weight,
    max(height) as height
  from have
  group by usubjid
;
quit;

 

novinosrin
Tourmaline | Level 20

Hi @Amy0223  Just UPDATE nonmissing values-



data have;
	infile datalines;
	input usubjid $ weight height;
	datalines;
A1	100	.
A1	.	162
A2	150	.
A2	.	190
;
run;


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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1562 views
  • 4 likes
  • 5 in conversation