CODEWITHSUNDEEP
sascoalesce
statsguyz
statsguyz

Reputation: 459

Merging SAS rows with COALESCE function

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.

Upvotes: 3

Views: 994

Answers (1)

Tom
Tom

Reputation: 51566

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;

Upvotes: 9

Related Questions