PROC SQL Update field from one table with information from another

valengvz — Sun, 08 Aug 2021 04:58:35 GMT

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!

Re: PROC SQL Update field from one table with information from another

Patrick — Sun, 08 Aug 2021 09:19:54 GMT

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.

topic PROC SQL Update field from one table with information from another in SAS Procedures

PROC SQL Update field from one table with information from another

Re: PROC SQL Update field from one table with information from another