## how to find missing value

Solved
Occasional Contributor
Posts: 9

# how to find missing value

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

Accepted Solutions
Solution
‎07-05-2018 07:00 PM
PROC Star
Posts: 1,334

## Re: how to find missing value

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;``````

All Replies
Super User
Posts: 9,840

## Re: how to find missing value

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;
run;```

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

Occasional Contributor
Posts: 9

## Re: how to find missing value

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

Super User
Posts: 9,840

## Re: how to find missing value

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?

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

data want;
run;```

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

Solution
‎07-05-2018 07:00 PM
PROC Star
Posts: 1,334

## Re: how to find missing value

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;``````
Super User
Posts: 8,279

## Re: how to find missing value

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;``````
☑ This topic is solved.