How to update one field from other table

[Cap2010]

below is the sql used it gives error.

proc sql;
update
tbl.OldTab
SET fld1 =
(select newfld
from
tbl.NewTab right join
tbl.OldTab
ON OldTab.fld1 = NewTab.newfld);
quit;

How to solve and update fld1 of oldtab with newfld from other table

Thanks,

Cap

 

[6656]

Cap,
In your code {ON OldTab.fld1 = NewTab.newfld}, the match join keys should be used instead of the field that you want update.

 

[Cap2010]

proc sql;
update
tbl.OldTab
SET fld1 =
(select newfld
from
tbl.NewTab right join
tbl.OldTab
ON OldTab.fld1 = NewTab.Oldfld);
quit;




I changed it and there is error

ERROR: You cannot reopen Tbl.OldTab.DATA for update access with member-level control because
Tbl.OldTab.DATA is in use by you in resource environment SQL.
ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain
exclusive access to the data set. This statement will not execute as the SQL option
UNDO_POLICY=REQUIRED is in effect.

 

[teralearner]

I have never used Update in proc sql. But the following code can do what you want. I used a different table name for clearity and easy comparison.

input id fld1;
datalines;
1 20
2 30
3 50
;

data Newtab;
input id newFld;
datalines;
1 10
3 30
4 50
;
proc sql;
create table updated as
select coalesce(Old.id, New.id) as id, case when New.id = . then fld1 else newfld end as updated
from Oldtab as old full join Newtab as new
on Old.id = New.id;
quit;

 

[bpowell555]

Proc SQL Update will only allow you to work with 1 table apparently - this is a poor limitation if I'm right! - although I know there are any number of alternative methods like that above,

cheers,

 

[teralearner]

Data step update statement also allow you to update one table base on another table's value.

Here is an example, again I use a different name for updated for easy comparison:

data Oldtab;
input id fld;
datalines;
1 20
2 30
3 50
;

data Newtab;
input id fld;
datalines;
1 10
3 30
4 50
;

data updated;
update Oldtab NewTab;
by id;
run;

 

[Cap2010]

Teralearner,

What you have informed, will this update only the field or the entire table.

Just want to update 1 field in Oldtable from newtable.

Fields in OldTable are more and NewTable has few fields.

Cap2010

 

[teralearner]

just keep the ones you want to update and the common key field. If you only want the records in Oldtab, you can use Dataset in option with a if statement.


data Oldtab;
input id fld1 fld2;
datalines;
1 20 9
2 30 8
3 50 7
;

data Newtab;
input id fld1 fld2;
datalines;
1 10 2
3 30 3
4 50 8
;

data updated;
update Oldtab NewTab(keep = id fld1);
by id;
run;

 

[Cap2010]

teralearner,

I am actually beginner in SAS and have SAS database.

My fields are
Table: OldTab (Many Fields are there in OldTab)
fld1 (Primary Key) - This needs to be changed
..
..
..


Table: NewTab (Has only two fields)
NewFld1 (having new value)
OldFld Value same as fld1 of OldTab

All needs to change in Table - OldTab is fld1 from
Table NewTab is NewFld1, comparing OldTab.fld1 = NewTab.Newfld1

Your statement
--------------
data Oldtab;
input id fld1 fld2; (Have to change "id&quot
datalines;
1 20 9 (is it have to input all these records)
2 30 8
3 50 7
;

data Newtab;
input fld1 fld2;
datalines;
1 10 2
3 30 3
4 50 8
;

data updated;
update Oldtab NewTab(keep = id fld1);?????
by id;
run;

Which is the best way to complete this important work as it is stopping all other process till this work is in not complete

Need urgent help.

Cap

 

[teralearner]

Are you sure you want to change by-variable? Could you give me an example? 2 or lines from Oldtab, 2 or 3 lines from Newtab and the final result you want. It is much easier to walk through detailed example than abstract explaination.

 

[Cap2010]

Teralearner,

Below is the example

OldTable
ItemId Description
A01 dkekdd
A02 fkjowr
B02 oiu3gdf
B03 fsdfoufsd

NewTable (Contains New ID)
OldId NewID
A01 AC1
A02 AI2
B02 BC2
B03 BB3

In old table "Item Id" value will change to "Newid" given in NewTable

Cap2010

 

[teralearner]

Two solutions:
First I read in you exaples:

data OldTable;
input ItemId $ Description :$50.;
datalines;
A01 dkekdd
A02 fkjowr
B02 oiu3gdf
B03 fsdfoufsd
;
data NewTable;
input OldId $ NewID $;
datalines;
A01 AC1
A02 AI2
B02 BC2
B03 BB3
;

Now solution 1:

proc sql;
create table updated as
select b.NewID as ItemId, a.Description
from OldTable as a inner join NewTable as b
on a.ItemId = b.Oldid;
quit;

Solution 2:

proc sort data = OldTable (rename = (itemid = id));
by id;
run;
proc sort data = NewTable (rename = (Oldid = id));
by id;
run;

data merged;
merge Oldtable (in = a) NewTable(in= b);
by id;
if a and b;
rename Newid = ItemId;
drop id;
run;

 

[Cap2010]

teralearner,

Thanks, found have written below mentioned sql.

PROC SQL;
CREATE TABLE NEWTAB LIKE OLDTAB;
INSERT INTO NEWTAB
(ITEMID,
.....
)
SELECT NEWID,
......
FROM
OLDTAB AS OLD, NEWTABLE AS NEW
WHERE OLD.ITEMID = NEW.OLDID;
QUIT;


When I used your statement, it has given me below error

ERROR: Indexed data set cannot be sorted in place unless the FORCE option is used.
ERROR: Variable id already exists on file NEWTable.
ERROR: Invalid DROP, KEEP, or RENAME option on file NEWTable.
ERROR: BY variable id is not on input data set OldTable.
WARNING: The variable id in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The data set WORK.MERGED may be incomplete. When this step was stopped there were 0
observations and 35 variables.
WARNING: Data set WORK.MERGED was not replaced because this step was stopped.

Cap

 

[teralearner]

Did first solution work?

For the second one:

the first error --
ERROR: Indexed data set cannot be sorted in place unless the FORCE option is used.
means your dataset is indexed, if you wnat to sort the dataset you have to use force option. So sort for Oldtab was not sucsessful, and name changing from ItemId to ID did not happen.
You need to change to
proc sort data = OldTable out = OldTab(rename=(ItemId =id));
by Itemid;
run;
Since I don't index my dataset, I am not sure if you need force option or not. You can test it out yourself.

Second error:
ERROR: Variable id already exists on file NEWTable.
You already have a field called id in NewTable, Renaming another variable to ID make second sort unsucsessful.
You need to change to
proc sort data= NewTable (keep = OldId NewID) out = newTab(rename=(OldId = NewID));
by OldID;
run;

Third error:
Since first steps unsucsessful, Id is not in OldTable and the NewTable ID field is not the right one.

ERROR: Invalid DROP, KEEP, or RENAME option on file NEWTable.
ERROR: BY variable id is not on input data set OldTable.
WARNING: The variable id in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The data set WORK.MERGED may be incomplete. When this step was stopped there were 0
observations and 35 variables.
WARNING: Data set WORK.MERGED was not replaced because this step was stopped.

You need to chang to
Merge Oldtab(in = a) NewTab (in=b);

BTW,
Without seeing your dataset, I can only give you directions or logics, you need to learn how to debug your self.