topic Re: Need help with merge in SAS Programming

Need help with merge

pavan_reddy — Sun, 17 Aug 2014 09:56:45 GMT

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;

Re: Need help with merge

Ksharp — Sun, 17 Aug 2014 12:03:54 GMT

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

Re: Need help with merge

Tom — Sun, 17 Aug 2014 16:10:12 GMT

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.

Re: Need help with merge

pavan_reddy — Sun, 17 Aug 2014 17:17:06 GMT

Thanks a lot Xia, this helped me a lot