Help using Base SAS procedures

update statement inside a proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

update statement inside a proc sql

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

Accepted Solutions
Solution
‎01-19-2017 10:43 AM
SAS Employee
Posts: 7

Re: update statement inside a proc sql

[ Edited ]

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


All Replies
Super User
Posts: 9,662

Re: update statement inside a proc sql

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
Solution
‎01-19-2017 10:43 AM
SAS Employee
Posts: 7

Re: update statement inside a proc sql

[ Edited ]

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

Occasional Contributor
Posts: 10

Re: update statement inside a proc sql

Thank u so much . It worked Smiley Happy
New Contributor
Posts: 4

Re: update statement inside a proc sql

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;

Super User
Posts: 9,662

Re: update statement inside a proc sql

[ Edited ]

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 18410 views
  • 0 likes
  • 4 in conversation