Hi there, I'm trying to update some specific fields in a table(solicitudes) from oracle with information from another table(originacion_decision).
In the oracle table solicitudes I have two fields, score and decision_id, those fields are null and I have to update them with the fields score and decision from the table originacion_decision only with the matching ones
I'm using this code with proc sql procedure, but it still doesn't work, really don't know why I'm doing wrong.
The unique key is a combination of the fields tipo_identificacion, nro_identificacion, nro_solicitud, codigo_subproducto
libname sasvi oracle user=&user password= &pswd
path=&path
cas mysess sessopts=(caslib=Public);
libname Public cas caslib=Public datalimit=all;
caslib _all_ assign;
proc sort data=Public.originacion_decision out= Public.originacion_decision nodupkey;
by nro_identificacion tipo_identificacion nro_solicitud;
run;
proc sort data=sasvi.solicitudes out=Public.originacion_decision nodupkey;
by nro_identificacion tipo_identificacion nro_solicitud;
run;
proc sql;
update sasvi.solicitudes as ora set score=(select id.score from
Public.originacion_decision as id where ora.score=. and
ora.tipo_identificacion=id.tipo_identificacion and
ora.nro_identificacion=id.nro_identificacion and
ora.codigo_subproducto=id.codigo_subproducto and
ora.nro_solicitud=id.nro_solicitud),
decision_id=(select id.decision from
Public.originacion_decision as id where ora.decision_id="" and
ora.tipo_identificacion=id.tipo_identificacion and
ora.nro_identificacion=id.nro_identificacion and
ora.codigo_subproducto=id.codigo_subproducto and
ora.nro_solicitud=id.nro_solicitud)
where exists (select * from
Public.originacion_decision as id where
ora.tipo_identificacion=id.tipo_identificacion and
ora.nro_identificacion=id.nro_identificacion and
ora.codigo_subproducto=id.codigo_subproducto and
ora.nro_solicitud=id.nro_solicitud);
quit;
I'll be grateful if somebody help me!
What are you actually trying to achieve?
Based on your code the Oracle table you want to update is: sasvi.solicitudes
The table you use for the update is: Public.originacion_decision
Your 2nd proc sort creates the 2nd table from the 1st table so you're logically updating the Oracle table with itself. Is this what you want to do?
Just guessing: You are not using the primary key for the update so guessing you want to replace NULL values in a tuple with the populated values from the same tuple. If so then first thing to do is to formulate this SQL as exlicit pass-through SQL so that you're not moving data between Oracle and SAS.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.