Hi. I'm trying to link 1 field from one table to many fields form another table. Here's what I mean. See below the data first.
Table 1
Product_code Variable_1
V3B_A 34
V3B_B 67
V5T_A 12
V5T_B 11
Table 2
Product_code Variable_1
V3B_1 24
V3B_2 87
V3B_3 19
V5T_1 08
V5T_2 03
V5T_3 24
The final result Table 3, I would like to link/merge each Product_code in Table_1 to each of the Product_codes in Table_2 if the first 3 digits are the same.
Here's how the final table would look:
Table 3
Product_code_1 Product_code_2 Variable_1_1 Variable_1_2
V3B_A V3B_1 34 24
V3B_A V3B_2 34 87
V3B_A V3B_3 34 19
V3B_B V3B_1 67 24
V3B_B V3B_2 67 87
V3B_B V3B_3 67 19
V5T_A V5T_1 12 08
V5T_A V5T_2 12 03
V5T_A V5T_3 12 24
V5T_B V5T_1 11 08
V5T_B V5T_2 11 03
V5T_B V5T_3 11 24
But this is just a short sample. I actually have many product codes and many more variables.. I don't assume it should matter. Or is it as simple as just stripping the first 3 digits from both tables and merge?
Thanks.
proc sql;
select a.product_code as product_code1,b.product_code as product_code2,
a.variable_1 as variable_1_1,b.variable_1 as variable_1_2 from have1 a, have2 b
where scan(a.product_code,1,'_')=scan(b.product_code,1,'_');
quit;
You can get the Table3 easily by sql procedure using Cartesian product as follows.
proc sql;
create table Table3 as select a.Product_code as Product_code_1,
b.Product_code as Product_code_2,
a.Variable_1 as Variable_1_1,
b.Variable_1 as Variable_1_2
from Table1 as a, Table2 as b
where substr(Product_code_1,1,3)=substr(Product_code_2,1,3);
quit;
Do not go for a cartesian product. Yes it will solve logical all combinations. The cost is that it is not practical for using needing tremendous resources when using some more larger datasets. It is the one to be avoided learning dbms.
Google for "table lookup" as that it what you described.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.