Merging SAS rows with COALESCE function

Question

I am trying to combine the following rows in SAS. Here is the data:

StudentNumber   Test1   Test2   Test3
001             .       86      .
001             94      .       .
001             .       .       75
002             68      .       .
002             .       82      .
002             .       .       97

I'd like the rows to look like the following:

StudentNumber   Test1   Test2   Test3
001             94      86      75
002             68      82      97

I'm used to merging columns with the COALESCE function, but I'm not sure how to do this with rows.

Answer 1

You can use the UPDATE statement to do that. The update statement expects to have a source dataset with unique observations per BY group and a transaction dataset that could have multiple observations per BY group. Only the non-missing values of the transactions will change the values. The output will have one observation per BY group with all transactions applied.

You can use your existing data as both the source and the transaction datasets by adding the dataset option obs=0 to the first reference.

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