topic Summary functions are restricted to the SELECT and HAVING clauses only ? in SAS Programming

Summary functions are restricted to the SELECT and HAVING clauses only ?

Midi — Thu, 26 Sep 2019 12:26:57 GMT

Hello Everyone , I had this Error While I was Trying to run this Code , can anyone give me first an explanation why i do get it and then a hand to resolve it , that would be Great , Thank you.

proc sql;
create Table Proof as 
select c.id_user_uni , c.division_id as division_id , ro.cod as matr, count(*) as cont
      from Ods.R_Ord o
	  inner join DMC.cust c
	   on user_id = user_ID_dup
	  inner join ods.R_r ro
	   on ro.ordi_id = o.id_ordi AND RO.SERVICE = 0
	  inner join ods.R_matr m
	   on ro.matr_id = id_matrvand flag = 0
	  group by 
	      ID_User_Uni,
		  C.division_ID,
		  Ro.Cod
	      Having Count(*) > 1 
;
quit;

This first Query Fine For me , the problem lies in this one :

proc sql;
create Table Transpo as 
select id_user_uni, division_id, sum(cont) as tot_items_ident, max(cont) as max_items_ident
from Proof 
group by
      Id_User_Uni,
      Divisione_ID
Order by 
      Sum(Conta) DESC
 ;
quit;

Thanks in advance

Re: Summary functions are restricted to the SELECT and HAVING clauses only ?

novinosrin — Thu, 26 Sep 2019 12:31:47 GMT

Try to assign that in SELECT clause and DROP later i.e

proc sql;
create Table Transpo(drop=temp) as 
select id_user_uni, division_id, sum(cont) as tot_items_ident, max(cont) as max_items_ident,    Sum(Conta) as temp
from Proof 
group by
      Id_User_Uni,
      Divisione_ID
Order by 
      temp DESC
 ;
quit;

Re: Summary functions are restricted to the SELECT and HAVING clauses only ?

Midi — Thu, 26 Sep 2019 12:37:23 GMT

Thank's That Worked Perfectly