DATA Step, Macro, Functions and more

How to extract only 2 matches during a join?

Reply
Regular Contributor
Posts: 162

How to extract only 2 matches during a join?

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;

end;

end;

run;

 

 

 

Thanks in advance,

Regards,

Sheeba

PROC Star
Posts: 1,561

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

[ Edited ]

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;

    
Regular Contributor
Posts: 162

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


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
Ask a Question
Discussion stats
  • 2 replies
  • 197 views
  • 1 like
  • 2 in conversation