Summarizing with Proc SQL

[user24feb]

I am trying to summarize data using proc sql. If I use this subquery, it gives me the desired result:

Proc SQL;

  Create Table Daten.Query_1_Test_C As

  Select Plant_Combined,

         KeyDate_Month_Key,

         MaterialClass_key,

         Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3

  From

    (Select * From sqlserv.vFact_SalesOrder_DP 

     Where KeyDate_Month_Key in (201401 201402 201403) AND

           MaterialClass_key="JGG" AND

           Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]") AND

           IsFutureDeliveryDate eq 1 AND

     Ultimo_Filter_Reporting eq 1) 

  Group By Plant_Combined,

           MaterialClass_key,

           KeyDate_Month_Key  

  Order By KeyDate_Month_Key,          

           Plant_Combined,

           MaterialClass_key

   ;

Quit;

What I cannot understand though, is why this is not the same as:

Proc SQL;

  Create Table Daten.Query_1_Test_B As

  Select Distinct Plant_Combined,

         KeyDate_Month_Key,

         MaterialClass_key,

         Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3

  From sqlserv.vFact_SalesOrder_DP 

  Where IsFutureDeliveryDate eq 1 AND

         Ultimo_Filter_Reporting eq 1

  Group By Plant_Combined,

           MaterialClass_key,

           KeyDate_Month_Key

  Having KeyDate_Month_Key in (201401 201402 201403) AND

         MaterialClass_key="JGG" AND

         Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]")        

  Order By KeyDate_Month_Key,          

        Plant_Combined,

        MaterialClass_key

   ;

Quit;

Shouldn't "where" and "having" do the job of a subquery?

[RW9]

Well, the simple answer is you can't use where clauses with aggregate functions.  Hence Having was added to cover that hole.

[user24feb]

Thanks for your quick reply.

.. if I put the where part to the having part (and remove where completely) it still gives a weird result. Any ideas what is wrong with this code?

Proc SQL;

  Create Table Daten.Query_1_Test_B As

  Select Distinct Plant_Combined,

         KeyDate_Month_Key,

         MaterialClass_key,

         Sum(Quantity_OpenOrder_t_IsBillingNo) As Qty_OpenOrder_t Format=Commax15.3

  From sqlserv.vFact_SalesOrder_DP 

  Group By Plant_Combined,

           MaterialClass_key,

           KeyDate_Month_Key

  Having KeyDate_Month_Key in (201401 201402 201403) AND

         MaterialClass_key="JGG" AND

         Substr(Plant_Combined,1,6) in ("[0031]" "[0032]" "[0034]" "[0332]") AND       

         IsFutureDeliveryDate eq 1 AND

          Ultimo_Filter_Reporting eq 1       

  Order By KeyDate_Month_Key,          

        Plant_Combined,

        MaterialClass_key

   ;

Quit;

[RW9 · Accepted solution]

What you are seeing is trying to do filters on data which is being grouped.  The order by is probably messing with things as well -> so you are trying to sequence the data by something other than the grouping info.  Put simply:

select ...

from          (get your data with your filters)

group by KeyDate_Month_Key,          /* This will be ordered like this also */

               Plant_Combined,

               MatierialClass_Key;