Merging and replace values with two dataset having different number of columns

[tmdgus]

Please refer to two fake datasets

Data 1:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4000
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	2980

Data 2:

city	conditonA	conditionB	Value
0	1	2	4100
1	2	3	3000

Final Table:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4100
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	3000

Basically I want to replace value from data 2 to data 1.

Which method should be used? (data step/proc SQL)

Thanks in advance.,

[Reeza · Accepted solution]

Do year and State not matter? You can have cities with the same name in multiple states so unless these are IDs I can see this being problematic.

If you had year/state included I would say use a data step UPDATE but if you don't, use a SQL join and COLESCE().

proc sql;
create table want as
select t1.*, coalesce(t2.Value, t1.Value) as new_value
from data1 as t1
left join data2 as t2
on t1.city=t2.city and t1.conditionA=t2.ConditionA and t1.ConditionB=t2.ConditionB;
quit;

---

@tmdgus wrote:

Please refer to two fake datasets

Data 1:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4000
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	2980

Data 2:

city	conditonA	conditionB	Value
0	1	2	4100
1	2	3	3000

Final Table:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4100
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	3000

Basically I want to replace value from data 2 to data 1.

Which method should be used? (data step/proc SQL)

Thanks in advance.,

---

[tmdgus]

Thank you!