Help using Base SAS procedures

Summarizing with Proc SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 336
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,401

Re: Summarizing with Proc SQL

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,401

Re: Summarizing with Proc SQL

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: 336

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,401

Re: Summarizing with Proc SQL

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.

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

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