Match/compare by many conditions/categories

[fri0]

Hi, I have a big problem trying to find a solution for a requirement... I've come to think that it's not possible to do.

I have 2 tables showing almost the same information. One is institucional info and another client info (I've attached bot of them in excel format because I had to change some confidential info).

Well, my problem is:

The idea is compare is some clients are copying institutional behavior... how? I have to check if any client from opsmesclient has made at least half of same operations of any institution from opsmesinst. Each register is a operation.

For example, for institution 00117138 I have to find if any client have made at least half of same operations (id column). 00117138 has 39 operations, has any client 20 same operations??? . If there is a coincidence I have to made a flag to identify them or create a table with the coincidence or any way to identify them.

I hope you could help me because I have no idea how to do this comparison. Thank very much.

[Reeza]

How big is your data?

Can you program it for one iteration? Ie if you have one company and one institution can you solve the programming problem?

[Ksharp · Accepted solution]

If I understood what you mean.

proc import datafile='c:\temp\opsmesinst.xls' out=inst dbms=excel replace;run;
proc import datafile='c:\temp\opsmescli.xls' out=client dbms=excel replace;run;


proc sql;
 create table want as 
  select distinct inst,cli
   from inst as a,client as b
    where a.id=b.id
      group by inst,cli
       having count(*) ge (select count(*)/2 from inst where inst=a.inst);
quit;

Xia Keshan

[fri0]

Thank you very much! I could just try your code today and it works. I'm checking the results for corroborate they are correct.

PD. Thanks for your help, I was trying to make the code for only one case but it was not easy get it done. I tried to make counters for both tables and create an extra column with the max value, after that I could make the comparison between number of registers.