topic After merging, why does one file consist of all observations and increase in size? in SAS Data Management

After merging, why does one file consist of all observations and increase in size?

NonSleeper — Tue, 21 Apr 2015 05:43:09 GMT

Maybe the title is too vague, but here's the actual circumstance. Before merging, I have file number 1 with the size of N1 and file number 2 N2.

For the most part, N2 is a subset of N1. Some observations in N2 may not be in N1 as seen later.

After merging, the size becomes N, N > N1.

When I run a crosstab of index variables, it indicates that all observations in N come from file number 1. How can that be possible when N > N1?

Re: After merging, why does one file consist of all observations and increase in size?

Kurt_Bremser — Tue, 21 Apr 2015 05:57:40 GMT

How did you do the merge? (Code)

Re: After merging, why does one file consist of all observations and increase in size?

NonSleeper — Tue, 21 Apr 2015 06:14:50 GMT

Like this:

data merge;

merge file1 (in=x) file2 (in=y);

by ID;

index1=x;

index2=y;

run;

Then:

proc freq data=merge;

table index1*index2;

run;

Re: After merging, why does one file consist of all observations and increase in size?

Kurt_Bremser — Tue, 21 Apr 2015 06:24:33 GMT

You did not set a condition in your merge like

if x and y;

So you get all the records from both tables.

Example

dataset file1

dataset file2

result

Re: After merging, why does one file consist of all observations and increase in size?

NonSleeper — Tue, 21 Apr 2015 06:58:27 GMT

But we can always filter out the observations after merging.

Yet I think the problem here is that after merging, all observations were indicated to be from file 1; that is, in the crosstab, there were no cells where index1=0. That's OK if all observations in file 2 were subset of file 1, but then we expect that the sample size did not increase after merging, which however did increase (N > N1).

Re: After merging, why does one file consist of all observations and increase in size?

Kurt_Bremser — Tue, 21 Apr 2015 07:12:06 GMT

If dataset 2 is a subset of dataset 1 in terms of the by variable, but has multiple observations for one or more ID's present in dataset 1, then those ID's will be multiplied.

Example:

dataset A

ID var1

1 x

2 y

3 z

dataset B

ID var2

1 a

1 b

2 c

result:

ID var1 var2

1 x a

1 x b

2 y c

3 z

Re: After merging, why does one file consist of all observations and increase in size?

NonSleeper — Tue, 21 Apr 2015 07:21:52 GMT

Ah ha, that's the right hit. Thanks.