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!
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;
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;
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.
You're welcome😀
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;
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;
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.
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.
Ready to level-up your skills? Choose your own adventure.