update statement inside a proc sql

[Rose]

I want to update tableA.ColumnA with tableB.ColumnB where tableA.id=tableB.id.
Can I do this inside a proc sql? I tried the below code

proc sql;
UPDATE tableA SET ColumnA = (
SELECT ColumnB FROM tableB a
WHERE a.id =id)
WHERE a.id = id;
quit;

But this resulted in the error, ERROR: Unresolved reference to table/correlation name a. Message was edited by: Rose

[Ksharp]

Yes.But I am afraid you need 'select' statement.

[pre]
data op;
set sashelp.class;
where sex eq 'F';
run;

proc sql;
create table new_table as
select a.*,b.name as updated_name
from op as a,sashelp.class as b
where a.age eq b.age;
quit;
[/pre]



Ksharp

[LewisC_sas · Accepted solution]

Hello Rose,

Probably the most direct way to go about this is to use the UPDATE statement in PROC SQL. I will provide a short code example below, point out a potential problem with it, then will modify part of the example to provide more robust code.

data tableToBeUpdated;
length z $5;
input z $ id y; 
cards;
one 1 50
two 2 30
three 3 30
four 4 60
five 5 70
six 6 80
run;

data tableB;
input id newY; 
cards;
1 500
2 233
3 300
4 472
5 111
6 2010
run;

proc sql;

update tableToBeUpdated tableA
set y = (select newY
from tableB
where tableA.id = id);

quit;

proc print data=tableToBeUpdated; run;

The code above works just fine as long as tabelB contains *exactly one* row for each of the id's that are present in tableToBeUpdated. However, if tableB contains no updated values for some of the id's in tableToBeUpdated, the corresponding y values in tableToBeUpdated will be set to missing. This can be readily seen if the tableB dataaset above is replaced by this alternative tableB dataset:

data tableB;
input id newY; 
cards;
1 500
3 300
6 2010
run;

A way to protect against this situation is to add a WHERE condition to the UPDATE statement so that only the rows that are in tableToBeUpdated which have id's that are present in tableB have their y values replaced. This modification results in the following UPDATE statement:

proc sql;

update tableToBeUpdated tableA
set y = (select newY
from tableB
where tableA.id = id)
where id in (select id
from tableB);

quit;

qseries@sas

(Editor's note: also see this helpful reply from KSharp using the COALESCE function).

[Rose]

Thank u so much . It worked 🙂

[kmcnulty]

I was thinking based on this post that when I submit the following code it wouldn't update my speeds in my master table if it wasn't in my reference table but it seems to still set it to missing if it's not in the reference table. Am I missing something?

proc sql;

update mastertable tableA

set speed = (select Speed

from referencetable

where (tableA.device = device and tablea.daydate between min_daydate AND max_daydate));

where device in(select device from referencetable);

quit;

[Ksharp]

Hi.
Misunderstood what you mean.
You need to use Function 'coalesce' to updated ID.

data temp(keep=name age);
set sashelp.class;
select(name);
when ('Henry') age=1;
when ('James') age=2;
otherwise;
end;
run;
proc sql;
create table updated as
select a.name,coalesce(b.age,a.age)
from sashelp.class as a left join temp as b on a.name=b.name;
quit;

Ksharp

[tobyfarms]

Hello, I am trying to update an existing table in the column called "stratum" (which it is actually doing) which is about 129 records, but it is also changing the other values in that column to missing.   I tried using the logic provided by @LewisC_sas but it is still resolving the other values to missing.

Code attached.

proc sql;
UPDATE C3B.RP_SAMPLE2 as  A
SET STRATUM = (select STRATUM FROM WORK.SAMPLE_RESTRAT as B
WHERE A.servicepointid=b.servicepointid AND A.recorderid=b.recorderid AND A.Sample_ID=B.Sample_ID AND B.Sample_ID IN (17))
WHERE Stratum IN (Select stratum from WORK.SAMPLE_RESTRAT as B);
quit;
;