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;
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
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
Thanks a lot Xia, this helped me a lot
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.