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.
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
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?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: