how to find missing value

[walterwang]

I have a data set. such as I=1:8, j=1:16, k=1;L=1,the last k is number.

but if k=0 we don't have this observation.

What is the best way to add this observation?

Cheers

8 15 1 2 33
8 16 1 2 14
8 99 1 2 509
8 1 2 1 72
8 2 2 1 89
8 3 2 1 91
8 4 2 1 34
8 5 2 1 21
8 6 2 1 24
8 7 2 1 21
8 8 2 1 20
8 9 2 1 26
8 10 2 1 22
8 11 2 1 15
8 12 2 1 24
8 13 2 1 7
8 14 2 1 5
8 15 2 1 2
8 16 2 1 6

[RW9]

See the guidance on posting a new question below where you posted the question.  Provide test data in the form of a datastep, and show what you want out from that test data.  Your post as such does not contain enough information to answer.  at a guess:

data toadd;
  k=0;
run;

data want;
  set have toadd;
run;

This will add one row with k=0 to the data.

[walterwang]

sorry I did not make the question clear.

I have a dataset such as

m  n value

1 1 5

1 2 9

1 3 6

2 1 8

2 2 7

3 1 9

3 2 6

3 3 9

We should have a line 2 3 0. But we don't have it.

We need add this line  2 3 0. please don't use append 2 3 0. Because we have a lot of these miss 0.

Cheers

[RW9]

Thats great, but why do you need to add 2 3 0 or "Because we have a lot of these miss 0."?  I can't give you any answer other than append 2 3 0, as I have no information as to why that specific set should be added, or any information at all about these other ones?

My answer remains the same:

data toadd;
  k=0;
/* set the other ones you have not given any information about here */
run;

data want;
  set have toadd;
run;

 Then sort if necessary.  Without any information I can't say anything else.

[TomKari · Accepted solution]

Assuming your values of m and n are regular, this should do it. If the values of m and n aren't regular, I can't see how you would distinguish between data that needs a row inserted and data that doesn't.

Tom

data LookupTable;
	do m = 1 to 9999;
		do n = 1 to 3;
			output;
		end;
	end;
run;

data Have;
	Input m n value;
	cards;
1 1 5
1 2 9
1 3 6
2 1 8
2 2 7
3 1 9
3 2 6
3 3 9
run;

proc sql;
	create table Want as
		select L.m, L.n, H.value
			from LookupTable L left join Have H
				on L.m = H.m and L.n = H.n;
quit;

data Want;
	set Want;

	if missing(value) then
		value = 0;
run;

[Tom]

How do you know when you are missing an observation?

In general for this type of problem you need to make a skeleton dataset that has all the expected observations and then merge it with the actual data.

In SQL you could do it this way:

proc sql ;
create table want as 
select a.*,coalesce(b.K,0) as K
from 
  (select * 
   from (select distinct I from have)
      , (select distinct J from have)
  ) as a
left join have as b
on a.i = b.i and a.j = b.j
;
quit;