topic Re: How Many Numbers Two Variables Have in Common in SAS Programming

How Many Numbers Two Variables Have in Common

TerryC — Tue, 21 Jan 2020 04:03:35 GMT

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!

How to find out how many numbers two variables have in common

TerryC — Tue, 21 Jan 2020 03:59:07 GMT

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!

Re: How Many Numbers Two Variables Have in Common

Tom — Tue, 21 Jan 2020 04:43:56 GMT

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

Re: How Many Numbers Two Variables Have in Common

TerryC — Tue, 21 Jan 2020 05:42:24 GMT

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.

Re: How to find out how many numbers two variables have in common

Panagiotis — Tue, 21 Jan 2020 17:54:00 GMT

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;

Re: How to find out how many numbers two variables have in common

ErikLund_Jensen — Tue, 21 Jan 2020 17:59:32 GMT

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;