Merging two datasets conditional on 3 variables in SAS
<p>I am quite new to using SAS and would like to ask for your help on merging two datasets that do not contain one key identifier but require a combination of three conditions to be merged successfully.</p>

<p>I have two datasets:  </p>

<ul>
<li>Dataset 1 contains information on client visits including <code>StartDate</code>, <code>EndDate</code>, <code>EmployeeNumber</code> and <code>CustomerNumber</code>.  </li>
<li>Dataset 2 contains information on certain activities that may have been performed during the visits in addition to the "Date" when the activity has been carried out and <code>EmployeeNumber</code> and <code>CustomerNumber</code></li>
</ul>

<p>The goal is to keep the first dataset and add information from the second dataset in case it is available - based on a match of date, employee number and customer number.</p>

<p>It is important that the date variable in the activity dataset is just on day while in the visits dataset it is a range between start and end date. So, the data sets look like this: 
<a href="http://i.stack.imgur.com/GOLE6.jpg" rel="nofollow">Base datasets</a> </p>

<p>Logically, it should be a merge based on three conditions:  </p>

<ol>
<li>IF EmployeeNumber = EmployeeNumber  </li>
<li>IF CustomerNumber = CustomerNumber  </li>
<li>IF Date is between StartDate and EndDate<br>
Then: Update visits dataset with information from activity dataset</li>
</ol>

<p>So, the ideal new dataset would look like this:
<a href="http://i.stack.imgur.com/zmdmI.jpg" rel="nofollow">Intended result of merge</a><br>
If there is no entry in the activity dataset, the new data set should simply contain a blank for the respective variables. For all matches, it should add the respective variable.</p>

<p>When trying (rather simple) merge statements in SAS, I usually get an error message stating that "BY variables are not properly sorted on data set". Furthermore, I am not sure how to implement the conditions (in particular the match of date to a range) properly.</p>

<p>Can this sort of merge be done in SAS or SQL?<br>
Any help is greatly appreciated!!!</p>