BookmarkSubscribeRSS Feed
valengvz
Fluorite | Level 6

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!

1 REPLY 1
Patrick
Opal | Level 21

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.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 315 views
  • 0 likes
  • 2 in conversation