topic Re: compare two tables and find the participants in common in New SAS User

compare two tables and find the participants in common

knighsson — Wed, 07 Apr 2021 14:44:00 GMT

Hello,

I have two tables that both contain variables "participant sequence number" (value: 1-10000) and "participant group number" (value: 1-4). The sample of two tables are the same, so "participant sequence number" are same in two tables, but the methods of grouping are different, so their corresponding "participant group number" may be different between two tables.

I want to know how many participants that are included in the same group are in both tables? (for example， how many participants that are included in group 1 in the first table, and also included in group 1 in the second table) .

Could you tell me how to do this?

Thanks in advance.

Re: compare two tables and find the participants in common

sbxkoenk — Wed, 07 Apr 2021 16:00:21 GMT

Maybe a PROC FREQ?

Submit this as an example:

data have1;
 call streaminit(123);
 do participant_sequence_number = 1 to 10000;
  participant_group_number=floor(rand('uniform')*5);
  output;
 end;
run;
data have2;
 call streaminit(456);
 do participant_sequence_number = 1 to 10000;
  participant_group_number=floor(rand('uniform')*5);
  output;
 end;
run;
data want;
 merge have1(rename=(participant_group_number=participant_group_number1)) 
       have2(rename=(participant_group_number=participant_group_number2)); 
 by participant_sequence_number /* participant_group_number */;
run;

PROC FREQ data=want;
 tables participant_group_number1 * participant_group_number2;
run;
/* end of program */

Cheers,

Koen

Re: compare two tables and find the participants in common

ballardw — Wed, 07 Apr 2021 16:03:37 GMT

@sbxkoenk wrote:

Maybe a PROC FREQ?

Submit this as an example:

data have1;
 call streaminit(123);
 do participant_sequence_number = 1 to 10000;
  participant_group_number=floor(rand('uniform')*5);
  output;
 end;
run;
data have2;
 call streaminit(456);
 do participant_sequence_number = 1 to 10000;
  participant_group_number=floor(rand('uniform')*5);
  output;
 end;
run;
data want;
 merge have1(rename=(participant_group_number=participant_group_number1)) 
       have2(rename=(participant_group_number=participant_group_number2)); 
 by participant_sequence_number /* participant_group_number */;
run;

PROC FREQ data=want;
 tables participant_group_number1 * participant_group_number2;
run;
/* end of program */

Cheers,

Koen

I think the LIST option may make the output easier to read for the original question:

PROC FREQ data=want;
 tables participant_group_number1 * participant_group_number2/ list ;
run;

Re: compare two tables and find the participants in common

PGStats — Wed, 07 Apr 2021 17:46:17 GMT

Or use SQL:

proc sql;
select 
    a.participant_group_number, 
    count(*) as nbInBothTables
from
    have1 as a inner join
    have2 as b 
        on  a.participant_sequence_number = b.participant_sequence_number and 
            a.participant_group_number = b.participant_group_number
group by a.participant_group_number;
quit;

Using simulated data from @sbxkoenk ,

Re: compare two tables and find the participants in common

knighsson — Wed, 07 Apr 2021 19:11:41 GMT

Thank you! this is helpful!

Re: compare two tables and find the participants in common

knighsson — Wed, 07 Apr 2021 19:11:52 GMT

Thank you!

Re: compare two tables and find the participants in common

knighsson — Wed, 07 Apr 2021 19:12:05 GMT

Thank you! This is helpful!