Combine multiple observations with missing values into one

Terry — Mon, 23 Jan 2012 21:55:07 GMT

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!

Combine multiple observations with missing values into one

art297 — Mon, 23 Jan 2012 22:10:10 GMT

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;

Combine multiple observations with missing values into one

Terry — Mon, 23 Jan 2012 22:39:08 GMT

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

Combine multiple observations with missing values into one

art297 — Mon, 23 Jan 2012 23:22:34 GMT

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.

topic Combine multiple observations with missing values into one in SAS Procedures

Combine multiple observations with missing values into one

Combine multiple observations with missing values into one

Combine multiple observations with missing values into one

Combine multiple observations with missing values into one