BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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.

3 REPLIES 3
slchen
Lapis Lazuli | Level 10

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;

scdent
Obsidian | Level 7

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;

jakarman
Barite | Level 11

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 --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 773 views
  • 1 like
  • 4 in conversation