topic Re: How To use Where instead of Group by ? in SAS Programming

How To use Where instead of Group by ?

Midi — Fri, 27 Sep 2019 10:46:32 GMT

Hello Everyone , I wrote a query , that gives me this Output :

ID_User	Division_ID	Tot_Item	Max_Item
1988	3	100	50
1541	3	121	32
4451	3	333	10

(This is Just a sample obviously the Output Table contains 300000 rows approximatly)

And This is my Query :

proc sql;
create Table Output as 
select ID_User, Division_ID, sum(conta) as Tot_Items, max(Counts) as Max_Item
from (select c.ID_User , c.Div_ID as Division_ID, ro.code as Mat, count(*) as Counts
      from Ods.R_Ordini o
	  inner join DMC.Cust_Dupl c
	   on User_ID = ID_User
	  inner join ods.R_Nlines ro
	   on ro.Orders_Id = o.Id_Orders AND RO.SERVICE = 0
	  inner join ods.R_Mat m
	   on ro.Mat_Id = Id_Mat and flag = 0
	  group by 
	      ID_User,
		  C.Division_ID,
		  Ro.Code
	      Having Counts > 1
)
group by
      Id_User,
      Division_ID
Order by 
      Tot_Item DESC
 ;
quit;

So , What i want is to re-write this Query , but instead of the Group by i want to use the Where Condition , (WHERE=(DIVISION_ID=3)) this is the condition.

I tried several attempts , with some i got errors , and with others i did got an output , but the output was not like the original one.

any help would be much appreciated , thank you.

Re: How To use Where instead of Group by ?

novinosrin — Fri, 27 Sep 2019 11:39:03 GMT

Hi @Midi

What you do you want to accomplish in

"but instead of the Group by i want to use the Where Condition , (WHERE=(DIVISION_ID=3)) this is the condition."

I am trying to understand instead of

Do you mean you want to add

WHERE=(DIVISION_ID=3)) after the FROM clause?

Can you clarify?

Re: How To use Where instead of Group by ?

PaigeMiller — Fri, 27 Sep 2019 12:06:06 GMT

Generally, GROUP BY and WHERE are not interchangeable, they do very different things. So its not clear why you want to exchange one for the other.

Next, the construct (WHERE=(DIVISION_ID=3)) is a data set option which can only be applied to data sets, is that where you tried to use it? It won't work elsewhere in SQL code.

Also, if you're not getting something that works, show us the code you tried — better yet, show us the LOG, including all the code and any error messages.