topic Re: Many to Many Merge in SAS Programming

Many to Many Merge

robertrao — Tue, 30 Oct 2012 17:33:19 GMT

Hi Team,

I am doing a many to many merge and end up with the wrong results. Is there any way we can get it right without using the PROC SQL!!!

Regards

Re: Many to Many Merge

Linlin — Tue, 30 Oct 2012 17:37:50 GMT

post some sample data.

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 17:43:53 GMT

As long as you have large enough RAM to host one of two merging datasets, Hash() will be among most efficient:

data h1;

do id=1 to 3;

do var1=1 to 2;

output;

end;

run;

data h2;

do id=1 to 3;

do var2=3 to 5;

output;

end;

run;

data want;

if _n_=1 then do;

if 0 then set h2;

declare hash h2(dataset:'h2', multidata:'y');

h2.definekey('id');

h2.definedata(all:'y');

h2.definedone();

end;

set h1;

rc=h2.find();

do rc=0 by 0 while (rc=0);

output;

rc=h2.find_next();

end;

drop rc;

run;

proc print;run;

Haikuo

Re: Many to Many Merge

robertrao — Tue, 30 Oct 2012 18:04:00 GMT

While merging i used IF A !!!

For each ID the symbol variable spans 35 times in the first dataset. BUT the disease code for that patient ranges from 0-35 meaning he might not have a code or has 35 of the codes.

I am merging this with dataset B which has ID common But has more than 35 count for ID(may be a thousand 101's) because of the splitting of the dollar amount

Also in the Dataset B the dollar amount is broken down for each patient

He might also have less than 35 records

Dataset A

ID symbol code

101 1 abc

101 2 def

101 3

101 4

101 35

102

Dataset B

ID total broken dollar amt(to satisfy 500 in all 101's)

101 500 20

101 500 15

101 500 100

101 500 so on which totals 500

101 500

IF HE HAS ONLY 11 Records in the B dataset he is getting the dollar amount on the 11th record retained 35 times because of the first dataset having 35 observations for each patient

When there are 11 in the Dataset B I want only those 11 and not 35

Regards

Re: Many to Many Merge

AncaTilea — Tue, 30 Oct 2012 18:27:15 GMT

Hi.

You cannot do a many-to-many merge via data steps if you only have one common key variable (in your case, ID) because SAS does not know how to "match" the say 11 records from B to the 35 records in A?

So you need at least another "key" variable that will help you identify uniquely each record in your data set (s).

For example, it would be very helpful if the dollar amt breakdown would have a code associated with it. Then you could sort both data sets by ID Code, then merge them by ID Code.

PROC SQL would work very well on your problem.:smileycool:

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 18:36:48 GMT

if I am thinking what you are thinking, you probably need something from this thread: (Pay attention to Ksharp's solution):

Haikuo

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 18:40:03 GMT

I would categorize Hash() as part of Datastep implementation, and with Hash() and enough memory, I can hardly see anything that Proc SQL can do while data step can't.

Just my 2 cents,

Haikuo

Re: Many to Many Merge

robertrao — Tue, 30 Oct 2012 18:54:22 GMT

Hi,

Thanks for the detailed information. I would go through the thread and get back with you if I have any more questions.

Regards

Re: Many to Many Merge

robertrao — Tue, 30 Oct 2012 19:18:04 GMT

Hi,

Does this NOte means we are doing something wrong???

NOTE: MERGE statement has more than one data set with repeats of BY values????

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 19:21:20 GMT

I won't worry about it if you do have data sets like that. It basically says that both of your data sets have by variables that are duplicated.

Haikuo

Re: Many to Many Merge

AncaTilea — Tue, 30 Oct 2012 19:32:04 GMT

The NOTE means that SAS does not know how to "match" the records in the two data sets.

One should never ignore that note and instead perform a correct merge.

Re: Many to Many Merge

robertrao — Tue, 30 Oct 2012 19:43:08 GMT

I will put it this way.

I said if A and A dataset has 5000 observations.

I did a merge of A and B and final dataset has 5000 observations?? and I get that NOTE

Do I need to worry abt it??

Regards

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 19:45:01 GMT

Hi AncaTilea,

I have to disagree. The NOTE is merely a reminder: " Wait a sec, are you sure this is what you want?", SAS does KNOW how to proceed under this scenario, and it does it with decent consistency. The only thing one should be certain is that given the predicable behavior of SAS, you know what you are getting.

According this thread,

one can manipulate data step to do what they want, regardless what they want makes any common sense or not.

my 2cents,

Haikuo

Re: Many to Many Merge

Haikuo — Tue, 30 Oct 2012 19:47:50 GMT

Karun,

At this stage, I think it is better for you to lay out what exact outcome you are expecting, preferably with sample tables( both incoming and outcoming). Then we can start from there.

Haikuo

Re: Many to Many Merge

AncaTilea — Tue, 30 Oct 2012 19:51:35 GMT

Understood.

Re: Many to Many Merge

Reeza — Tue, 30 Oct 2012 19:52:03 GMT

It's a note letting you know that your by variables are not unique, so you may be ending up with more records than you started.

You should verify for some of the records with multiple values that its giving you what you want.

Usually having the same amount of records means that everything is OK, but once I've had the record count match but there were multiples of some and missing of others so it wasn't what I wanted but the counts just happened to balance out, so now I always check.

Re: Many to Many Merge

cau83 — Fri, 02 Nov 2012 14:32:12 GMT

I used to have this problem as well but then read a SAS paper a few months ago where the author came up with a novel approach to do a many-to-many merge in a data step. I will post a link to the paper but also show code that I've created and explain how I use it as an extra example.

Many to Many Merge, Without SQL? By David Franklin

http://www.pharmasug.org/proceedings/2011/TU/PharmaSUG-2011-TU05.pdf

My sample code is attached. I am merging a data set that has a daily forecast over two weeks (each day of the week then appears multiple times) to a data set that contains an hourly expected percentage of the total daily volume for each week day-- this is how we get to an hourly forecast. So the data sets are merged by the variable in each data set called 'day' which is the name of the weekday (Monday, Tuesday, etc.). The "merge" is indicated by the "if day=_day" inside the first do loop level.

Forecast, the daily number, is multiplied by the pct from the second table and then explicitly outputted only when the days match.

The result is a new data set that has, instead of one record per day, a record for each hour of each day with the hourly forecast.