turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Need help with merge

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-17-2014 05:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pavan_reddy

08-17-2014 08:03 AM

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

All Replies

Solution

08-17-2014
08:03 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pavan_reddy

08-17-2014 08:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

08-17-2014 01:17 PM

Thanks a lot Xia, this helped me a lot

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pavan_reddy

08-17-2014 12:10 PM

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.