Help using Base SAS procedures

connect 1 to many

Reply
Super Contributor
Posts: 396

connect 1 to many

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.

Super Contributor
Posts: 275

Re: connect 1 to many

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;

New Contributor
Posts: 4

Re: connect 1 to many

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;

Valued Guide
Posts: 3,208

Re: connect 1 to many

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 1 like
  • 4 in conversation