topic Re: proc sql join in SAS Programming

proc sql join

sklal — Sun, 04 Feb 2018 12:24:12 GMT

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;

Re: proc sql join

LinusH — Sat, 03 Feb 2018 09:13:21 GMT

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".

Re: proc sql join

sklal — Sun, 04 Feb 2018 12:21:21 GMT

Thank you for the suggestion, I have edited the post accordingly. I didn't get any such message in the logs.

Re: proc sql join

Satish_Parida — Sun, 04 Feb 2018 14:20:28 GMT

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.

To eradicate the issue select all the columns from table INVMAST.INVMAST_01 with name except the variable COUPON_WEB_C instead of *
or rename as following

COALESCE(SUM(b.COUPON_WEB_C),0) AS COUPON_WEB_C1

Let us know if it worked for you.

Re: proc sql join

sklal — Mon, 05 Feb 2018 06:47:51 GMT

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;