Help using Base SAS procedures

Combine multiple observations with missing values into one

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Combine multiple observations with missing values into one

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!


Accepted Solutions
Solution
‎01-23-2012 05:10 PM
PROC Star
Posts: 7,360

Combine multiple observations with missing values into one

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;

View solution in original post


All Replies
Solution
‎01-23-2012 05:10 PM
PROC Star
Posts: 7,360

Combine multiple observations with missing values into one

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;

Occasional Contributor
Posts: 6

Combine multiple observations with missing values into one

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

PROC Star
Posts: 7,360

Combine multiple observations with missing values into one

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 258 views
  • 0 likes
  • 2 in conversation