How to work across two datasets in SAS

Question

I have two datasets described below

data1:    
$restaurant  $reviewers  
A            Tom  
B            Jack.Mary.Joan  
C            Tom.Joan  
D            Rose

data2 (sorted by the friends numbers):  
$user        $friends  
Tom          Joan.Mary.Jack  
Jack         Tom.Rose  
Mary         Tom  
Joan         Tom

The question is to calculate the overlap in the reviews of these users with the reviews of their friends.

Take an example of Tom, the restaurants Toms friends reviewed are B and C, from which C was also reviewed by Tom. So here the percentage is C/B+C = 1/2, so the overlap is 50%.

I think I need a loop to work across two datasets, but with very basic knowledge of SAS, I don't know how. Has anybody an idea?

Thank you very much.

Your question is extremely similar to [this one](https://stackoverflow.com/questions/46889637/how-to-check-percentage-overlap-in-sas/46890986#46890986)... — user2877959, Oct 27 '17 at 10:35
I am a beginner of SAS, and this question has kept me puzzles for several days. I am wondering if any experienced SAS users can solve this maybe very simple questions. Thank you very much!!! — Isabella, Oct 27 '17 at 11:06

score 0 · Answer 1 · answered Oct 27 '17 at 12:35

You should try something like this.

data reviews;
infile datalines dsd dlm=",";
input restaurant $ reviewer $;
datalines;
A,Tom
B,Jack
B,Mary
B,Joan
C,Tom
C,Joan
D,Rose
;
run;

data users;
infile datalines dsd dlm=",";
input user $ friend $;
datalines;
Tom,Joan
Tom,Mary
Tom,Jack
Jack,Tom
Jack,Rose
Mary,Tom
Joan,Tom
;
run;

proc sql;
create table want as
select t1.user
      ,sum(case when t3.restaurant=t2.restaurant then 1 else 0 end)/count(*) as percentage
from users t1
inner join reviews t2
  on t1.user=t2.reviewer
inner join reviews t3
  on t1.friend=t3.reviewer
group by t1.user
;
quit;

I did'nt get your 0,5 value for Tom, but maybe you have a mistake. So you can adapt the code as needed.

I followed the logic from here : How to check percentage overlap in SAS

How to work across two datasets in SAS

1 Answers1