Merging 2 SAS data sets, resulting in 0 observations

Question

I am trying to merge two datasets, set one contains ID and location, set two contains customer information, including the same ID as set one. I have tried a couple different methods to try to do this, the most recent I have been trying is:

Data Work.merged;
   merge Work.dataone (in = in1)
   Work.datatwo (in = in2);
by id location;
if in1 and in2;
Run;

This is outputting a dataset 'merged' that contains 0 observations, SAS output no error or warning messages in log.

Thank you for any help!

there is change that is no matchings. this means you do not have same id and location combination in both the tables Try this proc sql; select a.*, b.* from work.dataone a , work.datatwo where a.id =b.id and trim(a.location)=trim(b.location); Just check your data manually for few id in both the tables and check if you see anything unusual — Kiran
I got the sets to merge, finally. But another issue has arisen. My merged data set only displays the location value in the first row of each different id. So, I have approx. 200 rows with id = 72, but only the first(top) row displays the correct location, the rest are still left blank. — Andrew Engel
You should ask a new question for your new problem. If you are doing a 1 to many merge then make sure that the location variable is not already on the data set that has many observations per id. If it is then the values will overwrite anything merged on from the dataset that has only one observations per id. — Tom
Please edit your question and provide some (suitably fictitious) sample data that reproduces the problem when running your data step. This will make it much easier for people to provide a relevant answer. — user667489
For a many-to-many merge, you would probably want to use PROC SQL. — Björn

Quentin Quentin · Accepted Answer · 2018-08-05T22:23:05

Your IF statement is selecting only the records where there is a match from both datasets. If work.merged has 0 records, that means there are no matches. The easiest way to see the records that don’t match is to add a PUT statement to write those records to the log.

Consider:

data work.merged ;
  merge 
    work.dataone (in = in1)
    work.datatwo (in = in2)
  ;
  by id location;
  if not (in1 and in2) then put “Mismatch” (id location in1 in2)(=) ;
  if in1 and in2 ;
run ;

Merging 2 SAS data sets, resulting in 0 observations

1 Answers