Need help with merge

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Need help with merge

Hi,

When I am merging 2 datasets- A and B with 247 and 18 observations respectively as below , I am getting 248 observations in the dataset AB. Is this correct?

As much as I know, the no.of observations in the new dataset AB should be 247. Need help in solving this problem. Appreciate it.

data AB AB_dummy;

length common_variable $ 50;

merge A (in=a) B (in=b);

by common_variable;

if a then output AB;

if a and not b then output AB_dummy;

run;


Accepted Solutions
Solution
‎08-17-2014 08:03 AM
Grand Advisor
Posts: 9,596

Re: Need help with merge

One scenario is there are more group value in B than in A.

For example :

data a;
group=1;v=2; output;
group=2;v=2; output;
run;
data b;
group=1;w=2; output;
group=2;w=2; output;
group=2;w=2; output;
run;

data ab;
merge A (in=a) B (in=b);
by group;
if a ;
run;

if you strictly request the same number of obs with A , use this :

data ab;
a=0;
merge A (in=a) B (in=b);
by group;
if a ;
run;

Xia Keshan

View solution in original post


All Replies
Solution
‎08-17-2014 08:03 AM
Grand Advisor
Posts: 9,596

Re: Need help with merge

One scenario is there are more group value in B than in A.

For example :

data a;
group=1;v=2; output;
group=2;v=2; output;
run;
data b;
group=1;w=2; output;
group=2;w=2; output;
group=2;w=2; output;
run;

data ab;
merge A (in=a) B (in=b);
by group;
if a ;
run;

if you strictly request the same number of obs with A , use this :

data ab;
a=0;
merge A (in=a) B (in=b);
by group;
if a ;
run;

Xia Keshan

Occasional Contributor
Posts: 8

Re: Need help with merge

Thanks a lot Xia, this helped me a lot

Super User
Super User
Posts: 6,383

Re: Need help with merge

Your questions can be rephrased as how can there be MORE observations output to AB than read from A when you only output to AB when the record was in A?

This can occur on a merge when one of the other datasets in the merge has more observations for the same by group.

Consider a simple example.  If A has 4 observations with GROUP values of (1 2 3 4) and B has 3 observations when GROUP values of (1 1 2 ) then your program will generate 5 observations to AB because GROUP 1 will have two observations.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 222 views
  • 4 likes
  • 3 in conversation