topic Re: how to find missing value in SAS Data Management

how to find missing value

walterwang — Wed, 04 Jul 2018 04:51:29 GMT

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

Re: how to find missing value

RW9 — Wed, 04 Jul 2018 07:37:38 GMT

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.

Re: how to find missing value

walterwang — Wed, 04 Jul 2018 22:58:25 GMT

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

Re: how to find missing value

RW9 — Thu, 05 Jul 2018 07:35:54 GMT

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.

Re: how to find missing value

TomKari — Thu, 05 Jul 2018 17:37:10 GMT

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;

Re: how to find missing value

Tom — Thu, 05 Jul 2018 18:50:12 GMT

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;