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
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;
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.
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
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.