Combine multiple observations with missing values into one

[Terry]

Hi, everyone! I've got an ill-organized raw dataset and I met some troubles while trying to clean it.

1. There are multiple rows for one person and I'd like to combine them into one. It's like

    ID       Gender        Age         Var1         Var2         Var3

    1            F               .             .                5            10

    1            .               25           .                 .              .

    1            .                .             6                .              .

And the problem is that the missing pattern is not consistent across individuals. That is, for another individual, the data may look like

    ID       Gender        Age         Var1         Var2         Var3

    1            F              25           6                .               .

    1            .                .              .                5              .

    1            .                .             .                .             10

So by far what I can do with it is subsetting the data into many small non-missing datasets including ID and another variable, then remerging them by ID. Since there are many variables in the dataset, it is too time-consuming. Is there any simple way or command that can combine the data into one row for one individual?

2. The other scenario is that there are partly duplicated observations, which look like

   ID       Gender        Age         Var1         Var2         Var3

    1            F               .             .                5            10

    1            F              25          6                5            10

I hope to retain the observation with the most complete information and delete the duplicates. The only way I know about eliminating duplicates is using PROC SORT with NODUP options, but it seems that it does not work here. I feel that it can be solved in a similar way as for problem 1, but I don't know how.

Thank you!

[art297 · Accepted solution]

I think that something like the following may be all that you will need to clean that part of the file up:

data have;

  input ID Gender $ Age Var1 Var2 Var3;

  cards;

1 F  .  . 5 10

1 .  5  . .  .

1 .  .  6 .  .

2 F 25  6 .  .

2 .  .  . 5  .

2 .  .  . . 10

3 F  .  . 5 10

3 F 25  6 5 10

;

proc sort data=have out=want dupout=dups nodupkey;

  by id;

run;

data want;

  update want dups;

  by id;

run;

[Terry]

Thank you! It works. It is a great idea to separate then update the data in such a way.

[art297]

And, actually, the task can be simplified further without the need for separating the file.

data want ;

  update have (obs=0 keep=id)

         have;

  by id;

run;

I got that from a posted tip from Art Carpenter that he attributes to our own Tom A.