Help using Base SAS procedures

proc sql update 2 fields conditionally at the same time ?

Reply
SAS Employee
Posts: 3

proc sql update 2 fields conditionally at the same time ?

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
Frequent Contributor
Posts: 127

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

Hi Valérie,

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

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;
Occasional Contributor
Posts: 14

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

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
Ask a Question
Discussion stats
  • 2 replies
  • 171 views
  • 0 likes
  • 3 in conversation