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

Reply
Occasional Contributor
Posts: 8

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

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?

 

Super User
Posts: 5,082

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

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.

Respected Advisor
Posts: 3,890

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

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

 

Super User
Posts: 9,681

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

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;
 

Valued Guide
Posts: 797

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

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;

Ask a Question
Discussion stats
  • 4 replies
  • 158 views
  • 2 likes
  • 5 in conversation