topic Re: 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 — Mon, 11 Nov 2019 15:52:33 GMT

Hello Everyone , I Have This Block of code that i'm trying to run :

Proc Sql;
Create Table Work.Tbl_Dt_Tip_Count As
      Select
            Prep.Date
          , Prep.Name
          , Count(distinct Code || name || put(Year, 8.)) As Count
	  From
	     (
		    Select 
			      A.Date
			    , B.Name
				, B.Code
				, B.Year
		 From 
	          Work.Want_Day A
 	  Left join
	          Ext_Data.Tbl_Date B
          
		  on  Date >= min(dt_start)
		  and Date <= Max(Dt_end)
   ) Prep
      Group by

            Date
          , Name
Quit;

but i ran onto this error :

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

any help would be much appreciated , thank u

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

Krueger — Mon, 11 Nov 2019 15:55:58 GMT

Does this run without error?

If so you may have to break this out into two datasets and then do your count.

Proc Sql;
Create Table Work.Tbl_Dt_Tip_Count As
      Select
            Prep.Date
          , Prep.Name
	  From
	     (
		    Select 
			      A.Date
			    , B.Name
				, B.Code
				, B.Year
		 From 
	          Work.Want_Day A
 	  Left join
	          Ext_Data.Tbl_Date B
          
		  on  Date >= min(dt_start)
		  and Date <= Max(Dt_end)
   ) Prep
      Group by

            Date
          , Name
Quit;

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

novinosrin — Mon, 11 Nov 2019 15:59:43 GMT

If I understand correctly, I am afraid summary functions are not permitted in an on clause, and so this complicates to further nest like



Proc Sql;
Create Table Work.Tbl_Dt_Tip_Count(drop=min max) As
      Select
            Prep.Date
          , Prep.Name
          , Count(distinct Code || name || put(Year, 8.)) As Count
	  From
	     (
		    Select 
			      A.Date
			    , B.Name
				, B.Code
				, B.Year
		 From 
	          Work.Want_Day A
 	  Left join
	          (select *,min(dt_start) as min, max(Dt_end) as max) B
          
		  on   min<=Date<=max
		 
   ) Prep
      Group by

            Date
          , Name;
Quit;