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.
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;
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;
Excelente... Muchas Gracias por el aporte...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.