topic Re: Merge/Join of two tables by row and column value in SAS Programming

Merge/Join of two tables by row and column value

Johnd1 — Mon, 26 Mar 2018 14:44:50 GMT

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!

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

ballardw — Mon, 26 Mar 2018 15:13:37 GMT

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

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.

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

SASUser_22 — Mon, 26 Mar 2018 15:53:05 GMT

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;