Update Table from another Table deppending of two Variables

Cristian_SAS — Mon, 10 Nov 2014 18:34:33 GMT

Hi community...

I need to resolve this very quickly.

I established an extended table for CONTACT_HISTORY table of Customer Intelligence Common Data Model. I want to update a variable, this is LAST_CONTACT_DT.

HERE IS THE EXTENDED TABLE:

CELL_PACKAGE_SK NUMBER(10) NOT NULL ,

CAMPAIGN_CD NVARCHAR2(30) NOT NULL,

CUSTOMER_ID NUMBER(10) NOT NULL ,

LAST_CONTACT_DT DATE NOT NULL,

CICLO_ID NVARCHAR2(2) NOT NULL,

END_MANAGEMENT_DT DATE NOT NULL

For each occurrence for every communication for some campaign, if the Customer exist, i need to update the value of the variable.

First, I try this...

PROC SQL;

UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA. WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM EXIST);

QUIT;

That code, update the value for each Customer in the table, but for each campaign (CAMPAIGN_CD) that the customer exist. I only need to update the variable which contain the same END_MANAGEMENT_DT f the campaign that is being processed .. so.. I posed the following:

PROC SQL;

UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA.

FROM LIB.CI_CONTACT_HISTORY_C_EXTENDED

INNER JOIN WORK.EXIST EXISTS

ON EXTENDED.CUSTOMER_ID = EXISTS.CUSTOMER_ID AND

DATEPART(EXTENDED.END_MANAGEMENT_DT) = EXISTS.CONTACT_DT;

QUIT; /*The datepart function is required. WORK is SAS Code and the extended table is ORACLE Schema.*/

But this, doesn´t work.

Has anybody knows how to do that? How to realice an update from inner join between this tables: which contains the Customers in the occurrence of the communication and the table which contains that customers for the last occurrence of the previous communication.

Re: Update Table from another Table deppending of two Variables

DBailey — Mon, 10 Nov 2014 19:09:33 GMT

PROC SQL;

UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED T1 SET LAST_CONTACT_DT = &FECHA.

WHERE EXISTS (SELECT * FROM EXIST WHERE CUSTOMER_ID=T1.CUSTOMER_ID AND CONTACT_DT = DATEPART(T1.END_MANAGEMENT_DT));

QUIT;

Re: Update Table from another Table deppending of two Variables

Cristian_SAS — Mon, 10 Nov 2014 19:25:01 GMT

Excelente... Muchas Gracias por el aporte...

topic Re: Update Table from another Table deppending of two Variables in SAS Programming

Update Table from another Table deppending of two Variables

Re: Update Table from another Table deppending of two Variables

Re: Update Table from another Table deppending of two Variables