topic proc sql update 2 fields conditionally at the same time ? in SAS Procedures

proc sql update 2 fields conditionally at the same time ?

sbxvab — Tue, 15 Feb 2011 15:55:48 GMT

Dear all,
Do you know whether it is possible to update conditionnally 2 fields in the same sql update statement ?
This kind of synthax gives an error :

proc sql &statusql noprint;
update decava.person
set death_date = (select iden_mortaldate from
ident_sel_1 where ident_sel_1.iden_inss = person.social_security_nr) where exists ( select iden_mortaldate from ident_sel_1
where ident_sel_1.iden_inss = person.social_security_nr) ,
smals_last_update_date = (select iden_lastupdt_datetime from
ident_sel_1 where ident_sel_1.iden_inss = person.social_security_nr)
where exists ( select iden_lastupdt_datetime from ident_sel_1
where ident_sel_1.iden_inss = person.social_security_nr)
;
quit;

Without the where exists statements, an ORACLE error occurs.

Any idea is welcome.

Valérie

Re: proc sql update 2 fields conditionally at the same time ?

Florent — Tue, 15 Feb 2011 18:08:08 GMT

Hi Valérie,

Could you please check whether the following code match your expectations ? 🙂

I hope it helps !

Kind regards,
Florent

proc sql noprint;
update decava.person a
set death_date = (select iden_mortaldate
from ident_sel_1
where iden_inss = a.social_security_nr),
smals_last_update_date = (select iden_lastupdt_datetime
from ident_sel_1
where iden_inss = a.social_security_nr)
where a.social_security_nr in (select iden_inss
from ident_sel_1)
;
quit;

Re: proc sql update 2 fields conditionally at the same time ?

Vasile01 — Tue, 15 Feb 2011 23:18:38 GMT

Hi Valérie,

I tested your code (excluding those supplementary where conditions) on SAS datasets and it seems to work fine. I would say you'd better and faster try to ask a question to SAS technical support about that.

Warm regards,
Vasile