Merge/Join of two tables by row and column value

[Johnd1]

Hi, 

Is there a way to join/merge two datasets/tables in wich one register in dataset B refers at the same time to a row  (condition 1) and  to a column (condition 2) of dataset A?:

Condition 1: b.City = b.getColumnName() AND 

Condition 2: b.Part_code  = a.Part_code

What I am looking for would be something equivalent to the getColumnName(), to be able to make the comparison at the same time by row and by column.

Datasets are as follows (simplified examples):

Dataset A: 

Part_code     Miami         LA

A_1               60000        38000

A_2               5000          2000

A_3               1000          60000

Dataset B:

Part_code     City

A_1               Miami

Desired output (joined):

Part_code     City       Part_stock

A_1               Miami     60000

Thank you very much in advance! 

[ballardw · Accepted solution]

I suspect you will have lots of issues but changing your data from

Dataset A: 

Part_code     Miami         LA

A_1               60000        38000

A_2               5000          2000

A_3               1000          60000

 To

Part_code City    Part_stock

A_1          Miami  60000

A_1          LA       38000

A_2          Miami  5000

A_2          LA       2000

Which proc transpose should accomplish easily.

Would be quite easy:

proc sql;
   select c.part_code, c.city, c.part_stock
   from  datab as b
         left join
         dataAtransposed  as c
         on b.part_code=c.part_code
         and b.city=c.city
   ;
quit;

Data where the field name contain information is generally going to be a problem for queries. Normalize the data with variables holding the values you need to reference will make things much easier in the long run.

We might be able to come up with a solution that works for 2 cities that would break when you add in a third, or fourth or fifth.

The above suggestion will work for any number of cities IF the data is structured correctly.

[SASUser_22]

Just run this query,

data test1;
input part_code$ miami la;
cards;
A_1 60000 38000
A_2 5000 2000
A_3 1000 60000
;
run;

proc transpose data = test1 out = test2;
by part_code;
var miami la;
run;

data test3;
input part_code$ city$;
cards;
A_1 Miami
A_1 LA
A_2 Miami
A_2 LA
;
run;

proc sql;
create table test4 as
select distinct
a.*
from test2 a
left join test3 b
on a.part_code = b.part_code;
quit;