BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fri0
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

3 REPLIES 3
Reeza
Super User

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
Super User

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
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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