topic Merging two datasets of different number of observations in SAS Programming

Merging two datasets of different number of observations

Greisas — Tue, 31 Oct 2017 13:17:07 GMT

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.

Re: Merging two datasets of different number of observations

PaigeMiller — Tue, 31 Oct 2017 13:24:32 GMT

Do the merge using a BY statement.

Re: Merging two datasets of different number of observations

Astounding — Tue, 31 Oct 2017 13:31:03 GMT

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

data want;

if _n_=1 then set B;

set A;

run;

Re: Merging two datasets of different number of observations

Kurt_Bremser — Tue, 31 Oct 2017 13:37:52 GMT

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.

Re: Merging two datasets of different number of observations

Greisas — Tue, 31 Oct 2017 13:40:18 GMT

Hi,
Thank you for the quick and helpful comments!

Re: Merging two datasets of different number of observations

Greisas — Tue, 31 Oct 2017 13:44:24 GMT

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.