SAS Data Management

SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop, SAS Data Preparation and others
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
walterwang
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Onyx | Level 15

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
Super User Tom
Super User

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

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.

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