SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to find missing value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

Posted in reply to walterwang

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: how to find missing value

Posted in reply to walterwang

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.

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
Super User
Posts: 9,840

Re: how to find missing value

Posted in reply to walterwang

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.

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

Re: how to find missing value

Posted in reply to walterwang

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

Re: how to find missing value

Posted in reply to walterwang

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.

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

Discussion stats
  • 5 replies
  • 172 views
  • 0 likes
  • 4 in conversation