topic How to extract only 2 matches during a join? in SAS Programming

How to extract only 2 matches during a join?

Sheeba — Tue, 18 Oct 2016 22:14:35 GMT

Hi,

I am executing the below step where for each record in dataset1 , all the records in the dataset2 are looped and join criteria is applied and the corresponding data is moved to matched and notmatched. For each record in dataset1 there are more than 80 matches in dataset2 . I am planning to capture only 2 matches . I have initialised a variable dups and increment by 1 for each match.

If the value of dup is already 2 , I don’t want further processing for the record in dataset1. Can I stop the data step iteration for that record and move to the next iteration of the data step. Is there any way i can modify the program to capture only 2 matches.?

data matched(index=(seq_num)) notmatched(index=(seq_num));

matchingrecordfound=0;

notmatchingfound=0;

set dataset1;

dups=0;

drop _company matchingrecordfound notmatchingfound dups;

if dups> 2 then

do;

/*How do I stop the current iteration of the data step and move to next record in dataset1 */

end;

DO i=1 TO xnobs;

SET dataset2 (rename=(company=_company)) NOBS=xnobs POINT=i;

/* steps to process data */

If company=_company then

do;

If abc=xyz and def=kkk then

do;

Dups+1;

Output matched;

End;

Else

do;

output notmatched;

end;

run;

Thanks in advance,

Regards,

Sheeba

Re: How to extract only 2 matches during a join?

ChrisNZ — Tue, 18 Oct 2016 23:40:06 GMT

Like this?



data MAIN(keep=COMPANY VAR1)
     DUPS(keep=COMPANY VAR2);
  do COMPANY=1 to 20;
    VAR1=COMPANY ;
    if COMPANY ne 3 then output MAIN;
    do VAR2=1 to 80;
      output DUPS;
    end;
  end;
run;

data MATCHED NOTMATCHED;
   merge MAIN (in=M)
         DUPS (in=D);
   by COMPANY;
   if D and ^M then do; 
     output NOTMATCHED; 
     return;
   end;
   if first.COMPANY then COUNT=0;
   COUNT+1;
   if COUNT <= 2 then output MATCHED;
   drop COUNT;
run;

Re: How to extract only 2 matches during a join?

Sheeba — Thu, 20 Oct 2016 00:09:55 GMT

Hi ChrisNZ,

Thanks a lot for the reply

Due to nature of the data ( many to many join) I need to go with two set statements..

I have figured out a solution .. I will post after testing ..

Thanks a lot for the suggestion .

Regards,
Sheeba