Many to Many Merge

[Reeza]

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.

[cau83]

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.