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?
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;
Well, the simple answer is you can't use where clauses with aggregate functions. Hence Having was added to cover that hole.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.