Hello All,
While using the left join to join invmast & cadv table to add two new columns coupon_pos as coupon_pos_c and coupon_web as coupon_web_c , i'm getting duplicate values from the columns of the right table (cadv). Kindly help.
/*INVMAST*/
Proc Sql;
Create Table INVMAST.INVMAST_01 as
Select
Case
When BV_LM = 'Y' Then (INV_DATE - Day(INV_DATE))
Else INV_DATE
END as INV_DATE format=DDMMYY8., BV_LM,
/*Case
When INV_TOT NE 0 Then Count(IN_CH)
Else 0
End*/ Count(IN_CH) as TOTAL_KIT_NOS, COALESCE(Sum(INV_TOT),0) as TOTAL_KIT_VALUE,
COALESCE(Sum(COUPON_WEB),0) as COUPON_WEB, COALESCE(Sum(COUPON_POS),0) as COUPON_POS,
IN_CH, LOCA_CODE, LOCA_NAME, ZONE From INVMAST where IN_CH IN ('N','I') AND CANCL NE 1
Group by INV_DATE, BV_LM, IN_CH, LOCA_CODE, LOCA_NAME,ZONE;
Quit;
/*CADV*/
PROC SQL;
CREATE TABLE CADV.CADV_01 AS
SELECT
Case
When BV_LM = 'Y' Then (INV_DATE - Day(INV_DATE))
Else INV_DATE
END as INV_DATE format=DDMMYY8.,COALESCE(SUM(COUPON_POS),0) AS COUPON_POS_C,COALESCE(SUM(COUPON_WEB),0) AS COUPON_WEB_C,
BV_LM,IN_CH,LOCA_CODE,LOCA_NAME,ZONE FROM CADV WHERE IN_CH="I" AND CANCL NE 1
GROUP BY INV_DATE,BV_LM,IN_CH,LOCA_CODE,LOCA_NAME,ZONE;
QUIT;
/*Joining INVMAST & CADV*/
Proc Sql;
Create Table INCADV.INCADV_01 as
Select a.*,COALESCE(SUM(b.COUPON_POS_C),0) as COUPON_POS_C,COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_C
FROM INVMAST.INVMAST_01 AS a LEFT JOIN CADV.CADV_01 as b
On a.INV_DATE = b.INV_DATE
Group by a.INV_DATE, a.LOCA_CODE, a.LOCA_NAME, a.ZONE,a.BV_LM,a.IN_CH;
Quit;
I figured that despite of having other common columns , I was only joining on INV_Date and that was the mistake. The following code worked.
/*Joining INVMAST & CADV*/
Create Table INCADV.INCADV_01 as
Select a.*
, COALESCE(SUM(b.COUPON_POS_C),0) as COUPON_POS_C
, COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_CFROM INVMAST.INVMAST_01 AS a LEFT JOIN CADV.CADV_01 as b
On a.INV_DATE = b.INV_DATE
and a.BV_LM = b.BV_LM
and a.IN_CH = b.IN_CH
and a.LOCA_CODE = b.LOCA_CODE
and a.LOCA_NAME = b.LOCA_NAME
and a.zone = b.zoneGroup by a.INV_DATE, a.LOCA_CODE, a.LOCA_NAME, a.ZONE, a.BV_LM, a.IN_CH;Quit;
When asking for, please try to simplify your code, especially the parts that you don't think has to do with the problem, like macro logic.
This in turn will help you at the same time trouble shoot.
If you think this has to do with GROUP BY, search the log for messages like "remerging with original data".
Thank you for the suggestion, I have edited the post accordingly. I didn't get any such message in the logs.
I believe the problem is with only this part of the code.
/*Joining INVMAST & CADV*/
Proc Sql;
Create Table INCADV.INCADV_01 as
Select a.*,COALESCE(SUM(b.COUPON_POS_C),0) as COUPON_POS_C,COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_C
FROM INVMAST.INVMAST_01 AS a LEFT JOIN CADV.CADV_01 as b
On a.INV_DATE = b.INV_DATE
Group by a.INV_DATE, a.LOCA_CODE, a.LOCA_NAME, a.ZONE,a.BV_LM,a.IN_CH;
Quit;
So the issue is column both present in table INVMAST.INVMAST_01 and CADV.CADV_01, so when you are joining you must be getting an warning message over it. SAS is taking the variable from INVMAST.INVMAST_01 insted of CADV.CADV_01 and hence you are getting error results.
COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_C1
Let us know if it worked for you.
I figured that despite of having other common columns , I was only joining on INV_Date and that was the mistake. The following code worked.
/*Joining INVMAST & CADV*/
Create Table INCADV.INCADV_01 as
Select a.*
, COALESCE(SUM(b.COUPON_POS_C),0) as COUPON_POS_C
, COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_CFROM INVMAST.INVMAST_01 AS a LEFT JOIN CADV.CADV_01 as b
On a.INV_DATE = b.INV_DATE
and a.BV_LM = b.BV_LM
and a.IN_CH = b.IN_CH
and a.LOCA_CODE = b.LOCA_CODE
and a.LOCA_NAME = b.LOCA_NAME
and a.zone = b.zoneGroup by a.INV_DATE, a.LOCA_CODE, a.LOCA_NAME, a.ZONE, a.BV_LM, a.IN_CH;Quit;
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.