topic Re: how to combine rows in SAS Programming

how to combine rows

Amy0223 — Mon, 16 Nov 2020 04:50:45 GMT

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!

Re: how to combine rows

hhinohar — Mon, 16 Nov 2020 05:23:39 GMT

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;

Re: how to combine rows

andreas_lds — Mon, 16 Nov 2020 06:55:30 GMT

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.

Re: how to combine rows

hhinohar — Mon, 16 Nov 2020 07:09:35 GMT

You're welcome😀

Re: how to combine rows

Kurt_Bremser — Mon, 16 Nov 2020 07:31:06 GMT

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;

Re: how to combine rows

novinosrin — Mon, 16 Nov 2020 08:26:10 GMT

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;