Solved
Contributor
Posts: 69

Match/compare by many conditions/categories

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.

Accepted Solutions
Solution
‎02-03-2015 04:13 AM
Super User
Posts: 10,848

Re: Match/compare by many conditions/categories

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

All Replies
Super User
Posts: 23,980

Re: Match/compare by many conditions/categories

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

Solution
‎02-03-2015 04:13 AM
Super User
Posts: 10,848

Re: Match/compare by many conditions/categories

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

Contributor
Posts: 69

Re: Match/compare by many conditions/categories

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.

🔒 This topic is solved and locked.