topic Re: many-to-many merge in SAS Programming

many-to-many merge

Ronein — Wed, 21 Sep 2022 13:34:25 GMT

Hello

I run a merge of many-to-one or one-to-one.

How can I tell SAS to stop running the code and get error of the Merge is Many-To-Many?

note:

The merge should be one-to-one or one-to-many but if the raw data is faulty then IT might happen that it will be many-to-many and in such case i want to tell sas stop running the code

Re: many-to-many merge

Kurt_Bremser — Wed, 21 Sep 2022 14:24:00 GMT

Before you do the merge, run a check step on the dataset which is supposed to have only unique entries:

%let non_unique = 0;
data _null_;
set have1;
by key;
if first.key ne last.key
then do;
  call symputx('non_unique',1);
  stop;
end;
run;

You can now use the macro variable to control further action.

If this shall be part of a batch job, you can use ABORT ABEND (in place of CALL SYMPUTX) with a predefined exit code to alert the scheduler.

Re: many-to-many merge

Tom — Wed, 21 Sep 2022 14:49:14 GMT

You can actually reset the IN= variables and make it so you can detect many to many merge situations in a data step merge.

So if it is not the first observation for a BY group and more than one of the IN= variables is true then you have an ID that has more than one observation contributed from more than one dataset.

data one;
  input id @@;
  var1=1;
cards;
1 2 3 3 3 4 
;
data two;
  input id @@;
  var2=1;
cards;
1 2 2 3 3 4 
;

data want ;
  merge one(in=in1) two(in=in2);
  by id;
  if first.id then row=0;
  row+1;
  if sum(in1,in2)>1 and not first.id then do;
      put 'MANY TO MANY MERGE ' id= row= in1= in2=;
  end;
  call missing(in1,in2);
run;

Log

770   data want ;
771     merge one(in=in1) two(in=in2);
772     by id;
773     if first.id then row=0;
774     row+1;
775     if sum(in1,in2)>1 and not first.id then do;
776         put 'MANY TO MANY MERGE ' id= row= in1= in2=;
777     end;
778     call missing(in1,in2);
779   run;

MANY TO MANY MERGE id=3 row=2 in1=1 in2=1
NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: The data set WORK.WANT has 7 observations and 4 variables.

Re: many-to-many merge

Astounding — Wed, 21 Sep 2022 15:08:10 GMT

Here's an approach you can take:

data want;
   in1=0;
   in2=0;
   merge a (in=in1) b (in=in2);
   by id;
   if first.id=0 and in1 and in2 then do;
      /* bad situation encountered ... end it how you wish but here is one way */
      put id=;
      stop;   /* or abort is also possible */
   end;
run;