problem in grouping the data

[BSL]

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;

[RW9]

Hi,

There is no attachment <note: ignore now, was added later>.  Also the code is hard to read.  You have some typos in there which jump out at me - you have a FROM ( SELECT * ... but no closing bracket for example.  Try simplifying your code somewhat.  Break the steps down.  You make no mention of why you are trying to group your data as you have no summary functions in there, so what value is there in grouping?  Do you mean ordering the data, or do you want distinct values?

Also, for instance, why use cast()?    cast(0 AS decimal(18, 2)) AS PrdGrpCode

can be written as

0 as PrdGrpCode

(if really necessary then add format=18.2)

for instance.  I have briefly updated the first part of your code to illustrate:

proc sql;
  create table WANT as
  select  *
  from    (select *
           from   sw_sale_ssf
           where (vwInvoiceData_SSF.IdType = 'S')
            and  (vwAccountMaster_SSF.AccSaleGrp <> 'XX')
            and  (vwAccountMaster_SSF.AccSaleGrp <> 'GOC')
/* At this point your logic seems all over the place, there is not logic operator, its not clear which parts are to be part of the or statement or the and etc.*/
           isnull(vwInvoiceData_SSF.Iddepotcode = 'ERP' OR
    vwInvoiceData_SSF.Iddepotcode IS NULL) and

    and (vwInvoiceData_SSF.Iddate between '2014-04-01' and '2014-11-30');
quit;