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

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

JithinJoe — Fri, 23 Dec 2016 14:20:02 GMT

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

2 54

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?

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

Astounding — Fri, 23 Dec 2016 20:25:24 GMT

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.

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

Patrick — Sat, 24 Dec 2016 00:36:56 GMT

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

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

Ksharp — Sat, 24 Dec 2016 04:11:35 GMT

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;

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

mkeintz — Sat, 24 Dec 2016 18:53:32 GMT

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;