Desktop productivity for business analysts and programmers

Match/compare by many conditions/categories

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

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: 9,856

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

View solution in original post


All Replies
Super User
Posts: 19,038

Re: Match/compare by many conditions/categories

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?

Solution
‎02-03-2015 04:13 AM
Super User
Posts: 9,856

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: 58

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 446 views
  • 0 likes
  • 3 in conversation