Adding in missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Adding in missing values

Hi all

My second question for today (thanks for the help last time):

I have a data set that looks something like this:

  datalines;

1 30 8

2 15 36

3 4.6 294

4 4.6 595

5 6 119

6 3.3 112

7 3.6 5

This is a complete observation. An incomplete observation looks like this:

datalines;

1 7 35

2 18 26

4 3.9 103

5 4.7 201

7 4 70

As you can see the third and sixth observations are missing.

I would like to write code that finds the gaps and fills them in, so that the incomplete observation would look like this:

datalines;

1 7 35

2 18 26

3 . .

4 3.9 103

5 4.7 201

6 . .

7 4 70

where the third and sixth row are inserted, with an index but dots for the missing values.

Thanks in advance!


Accepted Solutions
Solution
‎08-07-2013 05:44 AM
Super User
Posts: 5,441

Re: Adding in missing values

Posted in reply to ChrisCHPT

Generate a second table with all expected id values (1,2,3...n), and then do a full join with the original table.

Data never sleeps

View solution in original post


All Replies
Solution
‎08-07-2013 05:44 AM
Super User
Posts: 5,441

Re: Adding in missing values

Posted in reply to ChrisCHPT

Generate a second table with all expected id values (1,2,3...n), and then do a full join with the original table.

Data never sleeps
Occasional Contributor
Posts: 6

Re: Adding in missing values

Thanks LinusH, that's a very simple solution.

Contributor
Posts: 25

Re: Adding in missing values

Posted in reply to ChrisCHPT

Try the following:

data incomplete;

  input id field1 field2;

  datalines;

1 7 35

2 18 26

4 3.9 103

5 4.7 201

7 4 70

;

run;

proc sql;

  select max(id) into : max_id from work.incomplete;

quit;

data temp;

  do id = 1 to &max_id;

  output;

  end;

run;

proc sql;

  create table complete as

  select temp.id, field1, field2

  from incomplete

  right join temp

  on incomplete.id = temp.id;

quit;

Attachment
🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 3 in conversation