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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.