DATA Step, Macro, Functions and more

Merging two datasets of different number of observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Merging two datasets of different number of observations

Hello,

I have one dataset that contains 1,000 observations and 50 variables - Data A.

I also have another dataset with only one observation and 30 variables - Data B.

The variables in Data B are parmeters that I want to add to all observations in Data A.

I tried to merge/set the two datasets but it only gives me first observation in A with the 1 observation in B and the other observations with missing value.

As You can see in the photo, Data A ends with variable P1000 and Data B starts with variable SM.

My question is, How do I dulplicate the one observation in B so all the observations in A will have it as a variable?

 

Thanks,

Matan.  


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 5,518

Re: Merging two datasets of different number of observations

To add one observation in B to all observations in A:

 

data want;

if _n_=1 then set B;

set A;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,934

Re: Merging two datasets of different number of observations

Do the merge using a BY statement.
Occasional Contributor
Posts: 9

Re: Merging two datasets of different number of observations

Posted in reply to PaigeMiller
Hi,
Thanks for the quick comment.
The problem is that Data A contains ID's, but Data B doesn't, so I can't use the BY statement.
Anyway, Astounding gave me a way.
Thanks again.
Solution
4 weeks ago
Super User
Posts: 5,518

Re: Merging two datasets of different number of observations

To add one observation in B to all observations in A:

 

data want;

if _n_=1 then set B;

set A;

run;

Occasional Contributor
Posts: 9

Re: Merging two datasets of different number of observations

Posted in reply to Astounding
Hi,
Thank you for the quick and helpful comments!

Super User
Posts: 7,866

Re: Merging two datasets of different number of observations

Consider these two methods:

data a;
input var1 var2 var3;
cards;
1 2 3
4 5 6
7 8 9
;
run;

data b;
input var4 var5;
cards;
10 11
;
run;

data want1;
set a;
_x_ = 1;
set b point=_x_;
run;

proc sql;
create table want2 as
select * from a,b;
quit;

The point=method rereads dataset b in every iteration, and therefore overrides the problem caused by the automatic "set to missing" that happens at the start of a datastep iteration.

SQL will always build a cartesian product on its own.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 195 views
  • 6 likes
  • 4 in conversation