DATA Step, Macro, Functions and more

Merge/Join of two tables by row and column value

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Merge/Join of two tables by row and column value

[ Edited ]

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! 

 

 


Accepted Solutions
Solution
‎03-26-2018 12:14 PM
Super User
Posts: 13,084

Re: Merge/Join of two tables by row and column value

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.

View solution in original post


All Replies
Solution
‎03-26-2018 12:14 PM
Super User
Posts: 13,084

Re: Merge/Join of two tables by row and column value

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.

Occasional Contributor
Posts: 6

Re: Merge/Join of two tables by row and column value

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 125 views
  • 1 like
  • 3 in conversation