topic Merging and replace values with two dataset having different number of columns in SAS Procedures

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

tmdgus — Tue, 25 May 2021 15:39:04 GMT

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.,

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

Reeza — Tue, 25 May 2021 16:05:18 GMT

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.,

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

tmdgus — Tue, 25 May 2021 16:38:51 GMT

Thank you!