BookmarkSubscribeRSS Feed
nkm123
Calcite | Level 5

Hi, I have following datasets and variables and need to match variables from one dataset to another dataset.

 

Requirement :- Need to match var1 to var5 from test1 dataset with var1 to var5 in test2 dataset. If any value from var1 to var5 is matching with any variable from var1 to var5 in test2 then have this id in output. I need this to be done in sql as this is just sample of existing code which is written in sql. 

 

I need this code to be modified after on t1.id = t2.id. Is there any simple way to get the result instaed of take one variable and try to match with var1 to var5 from second dataset. 

 

Missing and 0 should not match. 

 

proc sql;
select t1.id, "YES" as match, t1.sales_id from test1 t1 join test2 t2
on t1.id = t2.id ;
run;

 

 

 

data test1;
input id var1 $ var2 $ var3 $ var4 $ var5 $ sales_id;
datalines ;
1 A B C D E 101
2 F G H 0 0 102  
3 I J 0 0 0 103 
4 N M Q 0 0 104 
;
run;

data test2;
input id var1 $ var2 $ var3 $ var4 $ var5 $;
datalines ;
1 A 0 0 0 0
2 0 0 H G 0   
3 L M 0 0 0  
4 P A N 0 0  
;
run;

data want;
input id match $ sales_id;
datalines ;
1 YES 101
2 YES 102
4 YES 104
;
run;

proc sql;
select t1.id, "YES" as match, t1.sales_id from test1 t1 join test2 t2
on t1.id = t2.id ;
run;

 

Thanks

2 REPLIES 2
user24feb
Barite | Level 11

I would "transpose" the 2 datasets and merge afterwards:

 

data test1;
input id var1 $ var2 $ var3 $ var4 $ var5 $ sales_id;
datalines ;
1 A B C D E 101
2 F G H 0 0 102  
3 I J 0 0 0 103 
4 N M Q 0 0 104 
;
run;

data test2;
input id var1 $ var2 $ var3 $ var4 $ var5 $;
datalines ;
1 A 0 0 0 0
2 0 0 H G 0   
3 L M 0 0 0  
4 P A N 0 0  
;
run;

Data test1;
  Set test1;
  Array V Var1-Var5;
  Do over V;
    Var=V;
	Output;
  End;
  Drop Var1-Var5;
Run;

Data test2;
  Set test2;
  Array V Var1-Var5;
  Do over V;
    Var=V;
	Output;
  End;
  Drop Var1-Var5;
Run;

Proc SQL;
  Create Table Want As Select Distinct t1.id, t1.sales_id
    From test1 t1
	Left Join test2 t2
	On t1.id=t2.id
	Where t1.Var ne '0' AND t2.Var ne '0' AND t1.Var eq t2.Var;
Quit;
PGStats
Opal | Level 21

That's the problem with wide data. If var1 can be matched with var2, why aren't they the same variable? SQL queries are much simpler with normalised data structures.

PG

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 897 views
  • 2 likes
  • 3 in conversation