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.
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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.