BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
linda0910
Obsidian | Level 7

I have a dataset with 2000 observations. It contains ID, var1, var2.

Var1="11,21,43,35" or "11" or any combination of these numbers. sometimes just one number, sometimes two numbers. 

Var2="20,21,35,43" or  or any combination of these numbers. sometimes just one number, sometimes two numbers. 

I would like to generate a new variable to find any overlap or matching numbers between these two variables. 

 

For example, when var1="11,21,43,25" and var2="20,21,35,43"  then matching="21";

when var1="11" and var2="35" then matching=.;

when var1="11,21,35" and var2="21,35,43" then matching="21,35".

 

Data want;
input ID var1 $ var2 $;
datalines;
1 11,21,43,35 11
2 11,21,43,25 20,21,35,43
3 11 35
4 11,21,35 21,35
5 21 35,21,43
;

 

Any suggestions would be greatly appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
Data have;
input ID var1 :$12. var2 :$12.;
datalines;
1 11,21,43,35 11
2 11,21,43,25 20,21,35,43
3 11 35
4 11,21,35 21,35
5 21 35,21,43
;
data want;
	set have;
	nvar1=countw(var1,',');
	nvar2=countw(var2,',');
	length match $ 30;
	match=' ';
	do i=1 to nvar1;
		do j=1 to nvar2;
			if scan(var1,i,',')=scan(var2,j,',') then match=catx(',',match,scan(var1,i,','));
		end;
	end;
	drop i j nvar:;
run;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
Data have;
input ID var1 :$12. var2 :$12.;
datalines;
1 11,21,43,35 11
2 11,21,43,25 20,21,35,43
3 11 35
4 11,21,35 21,35
5 21 35,21,43
;
data want;
	set have;
	nvar1=countw(var1,',');
	nvar2=countw(var2,',');
	length match $ 30;
	match=' ';
	do i=1 to nvar1;
		do j=1 to nvar2;
			if scan(var1,i,',')=scan(var2,j,',') then match=catx(',',match,scan(var1,i,','));
		end;
	end;
	drop i j nvar:;
run;
--
Paige Miller
linda0910
Obsidian | Level 7
Thanks a lot. It works perfectly.
How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1844 views
  • 2 likes
  • 2 in conversation