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!
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.