How Many Numbers Two Variables Have in Common

[TerryC]

Hi, I am trying to find out how many numbers two variables have in common.
For example, I have data as follows,

#    Variable_1                Variable_2
1    |14|22|58|59|82|       |17|58|
2    |7|35|79|                   |27|35|45|66|79|93|
3    |2|13|                        |9|45|49|78|

I hope to get the results as follows,

#    Variable_1               Variable_2                   Output
1    |14|22|58|59|82|        |17|58|                          1
2    |7|35|79|                    |27|35|45|66|79|93|      2
3    |2|13|                         |9|45|49|78|                  0

Thanks!

[TerryC]

Hi, I am trying to find out how many numbers two variables have in common.

For example, I have data as follows,

#                 Variable_1                 Variable_2

1                 |14|22|58|59|82|         |17|58|

2                 |7|35|79|                     |27|35|45|66|79|93|

3                 |2|13|                          |9|45|49|78|

I hope to get the results as follows,

#                 Variable_1                 Variable_2                      Output

1                 |14|22|58|59|82|         |17|58|                               1

2                 |7|35|79|                     |27|35|45|66|79|93|           2

3                 |2|13|                          |9|45|49|78|                       0

Thanks!

[Panagiotis]

Wrote this during lunch. Please test accordingly, I didn't have time to test completely. This should get you most of the way.

data raw;
	infile datalines dsd;
	input row var1:$20. var2:$20.;
	datalines;
1,|14|22|58|59|82|,|17|58|
2,|7|35|79|,|27|35|45|66|79|93|
3,|2|13|,|9|45|49|78|
;
run;

data test;
	set raw;
	Var1Num=countw(var1,'|')-1; /*Find number of numbers in the var1 column*/
/*Loop through how many numbers to test if one exists in var2*/
	do i=1 to Var1Num;
		TestValue=scan(var1,i,'|','r');/*loop through numbers*/
		if findw(var2,strip(put(TestValue,3.)))>0 then Found=1;/*If found then found=1*/
			else Found=0;
		output;
	end;
run;

/*Summarize the data*/
data final;
	set test;
	by row;
	if first.row then sum=0; /*Sum column set = 0 at the start of a group*/
	sum+found; /*Begin to sum if found*/
	if last.row then output; /*output the last row with the total sum*/
run;

[ErikLund_Jensen]

Hi @TerryC 

This is one way:

data have;
input No @3 Var1 $char20. @23 Var2 $char20.;
cards;
1 |14|22|58|59|82|    |17|58|
2 |7|35|79|           |27|35|45|66|79|93|
3 |2|13|              |9|45|49|78|
;

data want (drop=cVar1 cVar2 i j);
	set have;
	Count = 0;
	cVar1 = translate(var1,' ','|');
	cVar2 = translate(var2,' ','|');
	do i = 1 to countw(cVar1);
		do j = 1 to countw(cVar2);
			if scan(cVar1,i,' ') = scan(cVar2,j,' ') then Count + 1;
		end;
	end;
run; 

[Tom · Accepted solution]

Can we assume that both lists only have unique values?  Or is it possible for the same "number" to appear more than once in one of the strings (or both)?

You say you have numbers, but really you have strings.  By calling them numbers do you mean that is should consider the string 013 as a match for the string 13 since they both represent the same number.  Or can we just assume that leading (meaningless) zeros have already been removed from the strings?

data have ;
  row+1;
  length var1 var2 $200;
  input var1 var2;
cards;
|14|22|58|59|82|       |17|58|
|7|35|79|                   |27|35|45|66|79|93|
|2|13|                        |9|45|49|78|
;;;;

data want ;
  set have ;
  do index=1 to countw(var1,'|');
    count=sum(count,0^=indexw(var2,scan(var1,index,'|'),'|'));
  end;
  drop index;
run;

Results:

Obs    row    var1                var2                   count

 1      1     |14|22|58|59|82|    |17|58|                  1
 2      2     |7|35|79|           |27|35|45|66|79|93|      2
 3      3     |2|13|              |9|45|49|78|             0

[TerryC]

Thanks, Tom! It works perfectly well.

Yes, both lists have unique values. 

I think I should better call them strings since zeros have been removed from the strings.