topic Re: connect 1 to many in SAS Procedures

connect 1 to many

podarum — Fri, 01 Aug 2014 02:53:21 GMT

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.

Re: connect 1 to many

slchen — Fri, 01 Aug 2014 03:21:00 GMT

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;

Re: connect 1 to many

scdent — Fri, 01 Aug 2014 03:32:30 GMT

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;

Re: connect 1 to many

jakarman — Fri, 01 Aug 2014 06:21:01 GMT

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.