Updating a table with corresponding values in a second table with Proc SQl

[JithinJoe]

Hi, 

I need to update the variable from one dataset with another variable from second dataset, I have tried Proc SQL update command. but couldn't able to get it, I'm a beginner in proc sql.   

I have 2 data sets with 2 variables in it, 

DATASET_A.

ID   Value

1      10

1      11

1      23

2     11

2     98

2     54

3    10

3    30

4   44

DATASET_B

ID   Value

1      25

2     54

3    13

Now I need to update the values of DATASET_A with DATASET_B. So the desired output will be 

ID   Value

1      25

1      25

1      25

2     54

2     54

2     54

3    13

3    13

4   44

I have tried with the code 

proc sql;
update DATASET_A 
set value=(select value from DATASET_B  where DATASET_A .value=DATASET_B.value)
where ID in (select ID from DATASET_B);
quit;

Its giving me " ERROR: Subquery evaluated to more than one row."

what does it mean? Any help?

[Astounding]

As long as you are a beginner using SQL, let's use a different but very common SAS tool. 

data want;

merge dataset_a (in=in1) dataset_b (in=in2 rename=(value=value_from_b));

by id;

if in1;

if in2 then value = value_from_b;

drop value_from_b;

run;

The program relies on the data being in sorted order.  If that's not the case, you'll have to sort first.

[Patrick]

For Proc SQL here an example with explanation how this needs to look like with the SAS SQL flavour.

http://support.sas.com/kb/25/216.html

[Ksharp]

It should be A.ID=B.ID.


data DATASET_A;
input ID   Value;
cards;
1      10
1      11
1      23
2     11
2     98
2     54
3    10
3    30
4   44
;

data DATASET_B;
input ID   Value;
cards;
1      25
2     54
3    13
;

proc sql;
update DATASET_A 
set value=(select value from DATASET_B  where DATASET_A.id=DATASET_B.id)
where ID in (select ID from DATASET_B);
quit;
 

[mkeintz]

You can use the coalesce function, which takes the first non-missing value from a list of values.  The list in this case is "b.value,a.value", as below.  In other words, a.value is kept only when b.value is missing or absent:

proc sql;

  create table want as

    select coalesce(b.value,a.value) as value, *

    from a left join b on a.id=b.id;

quit;