how to combine rows

[Amy0223]

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!

[hhinohar]

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]

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]

You're welcome😀

[Kurt_Bremser · Accepted solution]

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]

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;