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!
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
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!
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;
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.