topic Re: Summarizing with Proc SQL in SAS Procedures

Summarizing with Proc SQL

user24feb — Mon, 11 Aug 2014 13:16:41 GMT

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?

Re: Summarizing with Proc SQL

RW9 — Mon, 11 Aug 2014 13:27:25 GMT

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

Re: Summarizing with Proc SQL

user24feb — Mon, 11 Aug 2014 13:39:45 GMT

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;

Re: Summarizing with Proc SQL

RW9 — Mon, 11 Aug 2014 13:49:57 GMT

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;