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
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;
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;
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 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.