Help using Base SAS procedures

Summarizing with Proc SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 340
Accepted Solution

Summarizing with Proc SQL

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?


Accepted Solutions
Solution
‎08-11-2014 09:49 AM
Super User
Super User
Posts: 7,979

Re: Summarizing with Proc SQL

Posted in reply to user24feb

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,979

Re: Summarizing with Proc SQL

Posted in reply to user24feb

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

Super Contributor
Posts: 340

Re: Summarizing with Proc SQL

Thanks for your quick reply. Smiley Happy

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

Solution
‎08-11-2014 09:49 AM
Super User
Super User
Posts: 7,979

Re: Summarizing with Proc SQL

Posted in reply to user24feb

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 189 views
  • 3 likes
  • 2 in conversation