proc sql update error expecting a set

[felicity1212]

Hi. I am trying to update 4 columns in one table with information from 4 columns in another table. The error message i received says:
ERROR 79-322: Expecting a SET.

ERROR 76-322: Syntax error, statement will be ignored.


Here is my code, can someone help me please?
THank you!!

PROC SQL;
UPDATE
WORK.YODDAYODDA slmbrs

JOIN DWH.MEMBERS mbrs ON slmbrs.HFMemberID = mbrs.membno

JOIN DWH.PROVIDER prov ON mbrs.pcpno = prov.provno

SET

slmbrs.pcpid = mbrs.pcpno,
slmbrs.pcpparent = prov.parent,
slmbrs.pcpfirstname = prov.pfstnam,
slmbrs.pcplastname = prov.plstnam;
QUIT;

 

[sasbuddy]

Hi,

It seems there is problem with the syntax;
you should you corelated subquery for this, as shown below.

CODE :

PROC SQL;
UPDATE
WORK.YODDAYODDA slmbrs
SET
slmbrs.pcpid = (select mbrs.pcpno where slmbrs.HFMemberID = mbrs.membno),
slmbrs.pcpparent = (select prov.parent where mbrs.pcpno = prov.provno ),
slmbrs.pcpfirstname = (select prov.pfstnam where mbrs.pcpno = prov.provno ),
slmbrs.pcplastname = (select prov.plstnam where mbrs.pcpno = prov.provno );
QUIT;

********************************************************************
However my suggestion would be as this is query is using multiple joins;
it better to join all the three tables in query creating a temporary dataset and then
making changes to that dataset.


sasbuddy

http://sites.google.com/site/sasbuddy/

 

[felicity1212]

the datasets are really large. idk if i should make a temporary dataset out of them.

I would like to work with the code that you gave me, thank you. but i still need to join the tables together to do the update. anyway you can help me with that portion too

I redid the code. and then got an error that says expecting an "="



PROC SQL;

UPDATE

WORK.leads slmbrs

SET

slmbrs.pcpid = mbrs.pcpno,
slmbrs.pcpparent = prov.parent,
slmbrs.pcpfirstname = prov.pfstnam,
slmbrs.pcplastname = prov.plstnam

FROM

slmbrs

INNER JOIN DWH.MEMBERS mbrs ON slmbrs.HFMemberID = mbrs.membno
INNER JOIN DWH.PROVIDER prov ON mbrs.pcpno = prov.provno;



QUIT;

 

[sasbuddy]

HI,

Please try following code.

PROC SQL;
create table leads as
select slmbrs.*, mbrs.pcpno, prov.parent, prov.pfstnam, prov.plstnam
from
WORK.leads slmbrs, DWH.MEMBERS mbrs, DWH.PROVIDER prov
where
slmbrs.HFMemberID = mbrs.membno
and mbrs.pcpno = prov.provno;
quit;

data leads(drop=pcpno parent pfstnam plstnam);
set leads;
if not missing(pcpno) then pcpid = pcpno;
if not missing (parent) then pcpparent = parent ;
if not missing (pfstnam) then pcpfirstname = pfstnam;
if not missing (plstnam) then pcplastname = plstnam;
run;

********************
I hope this will work. Best luck.


sasbuddy

http://sites.google.com/site/sasbuddy/