Dear Experts, I am trying to group two table TGT and SALE. TGT has grouped data and SALE has detailed level data so could anybody please help me to group these tables correctly. I am using below mentioned code. Sample data is attached in book.xls Regards Rahul proc sql; SELECT TgtYearMon, TgtYearMon_m, TgtYearMon_Y, TgHQCode, GrpCode, GrpSubCode,TgtQty, TgtQty_Avg, TgtRate, TgTSaSMGrp, TgTSaSSGrp, UnitCode, Iddepotcode, DepotName, IdPrdCode, IdPrdQty, SaleKg, IdPrdAmt, AccName, Iddate, ImDate, CityDesc, StateName, UomDesc, PrdAsgnCode, PrdName, PrdUOM, EmpName,ImNetAmtRO, PrdGrpCode, PrdWt, AccCity, IdPrdRate, Prd1KgGEq, IdPrdQtyF, ImType, IdType, UMeasure, NQty, value, AccSLgrpCatg, AccSlGrpZone, PrdSaSMGrp, PrdSaSSGrp FROM ( SELECT * FROM sw_sale_ssf WHERE (vwInvoiceData_SSF.IdType = 'S') AND (vwAccountMaster_SSF.AccSaleGrp <> 'XX') AND (vwAccountMaster_SSF.AccSaleGrp <> 'GOC') isnull(vwInvoiceData_SSF.Iddepotcode = 'ERP' OR vwInvoiceData_SSF.Iddepotcode IS NULL) and and (vwInvoiceData_SSF.Iddate between '2014-04-01' and '2014-11-30') GROUP BY Iddepotcode, IdPrdCode, AccName, Iddate, ImDate, CityDesc, StateName, UomDesc, PrdAsgnCode, PrdName, PrdUOM, EmpName, ImNetAmtRO, PrdGrpCode, PrdWt, AccCity, Prd1KgGEq, IdPrdQtyF, ImType, IdType, AccSLgrpCatg, AccSlGrpZone, PrdSaSMGrp, PrdSaSSGrp UNION SELECT TgtYearMon, TgtYearMon_m, TgtYearMon_Y, TgHQCode, GrpCode, '0' AS TgtQty, TgtQty_Avg, TgtRate, TgTSaSMGrp, TgTSaSSGrp, 'SSF' AS UnitCode, ' ' AS Iddepotcode, ' ' as DepotName, cast(0 AS decimal(18, 2)) AS IdPrdCode, cast(0 AS decimal(18, 2)) AS IdPrdQty, cast(0 AS decimal(18, 2)) AS SaleKg, cast(0 AS decimal(18, 2)) AS IdPrdAmt, ' ' AS AccName, cast(0 AS decimal(18, 2)) AS Iddate, cast(0 AS decimal(18, 2)) AS ImDate, ' ' AS CityDesc, ' ' AS StateName, ' ' AS UomDesc, ' ' AS PrdAsgnCode, ' ' AS PrdName, ' ' AS PrdUOM, ' ' AS EmpName, cast(0 AS decimal(18, 2)) AS ImNetAmtRO, cast(0 AS decimal(18, 2)) AS PrdGrpCode, cast(0 AS decimal(18, 2)) AS PrdWt, ' ' AS AccCity, cast(0 AS decimal(18, 2)) as IdPrdRate, cast(0 AS decimal(18, 2)) as Prd1KgGEq, cast(0 AS decimal(18, 2)) as IdPrdQtyF, ' ' AS ImType, ' ' AS IdType, 'Q' AS UMeasure, cast(0 AS decimal(18, 2)) AS NQty, cast(0 AS decimal(18, 2)) AS value, ' ' AS AccSLgrpCatg, ' ' AS AccSlGrpZone, ' ' AS PrdSaSMGrp, ' ' AS PrdSaSSGrp FROM V_Sas_SaleTgtMst where V_Sas_SaleTgtMst.TgtYearMon between '01Apr2014' and '30Nov2014') AS tgtsale; quit;
... View more