BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rose
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
LewisC_sas
SAS Employee

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

View solution in original post

6 REPLIES 6
Ksharp
Super User
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
SAS Employee

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
Calcite | Level 5
Thank u so much . It worked 🙂
kmcnulty
Obsidian | Level 7

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
Super User

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
Fluorite | Level 6

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;
;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 59503 views
  • 2 likes
  • 5 in conversation