topic Re: Summary functions are restricted to the select and having clauses only in Developers

Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 14:04:00 GMT

I want to display the number of project having x applications (in the dataset 1 row = 1 application, thats why I consider 1 ID_Project as 1 application) :

SELECT 
(CASE 
      WHEN COUNT(ID_Project) = 1 THEN 'Projects with 1 application'
      WHEN COUNT(ID_Project) = 2 THEN 'Projects with 2 applications'
      WHEN COUNT(ID_Project) = 3 THEN 'Projects with 3 applications'
      ELSE 'Projects with 3+ applications'
END), COUNT(ID_Project)
FROM TEST2

GROUP BY 
(CASE 
      WHEN COUNT(ID_Project) = 1 THEN 'Projects with 1 application'
      WHEN COUNT(ID_Project) = 2 THEN 'Projects with 2 applications'
      WHEN COUNT(ID_Project) = 3 THEN 'Projects with 3 applications'
      ELSE 'Projects with 3+ applications'
END);

I have the following error "Summary functions are restricted to the select and having clauses only". I know that I can't use count in the group by but I think Its mandatory if I want to have categories.

Thanks for your help !

Re: Summary functions are restricted to the select and having clauses only

PaigeMiller — Thu, 15 Apr 2021 14:14:50 GMT

I want to display the number of project having x applications

PROC FREQ would be a whole lot easier.

proc format;
    value custf 1='Projects with 1 application'
         2='Projects with 2 applications'
         3='Projects with 3 applications'
         4-high='Projects with >3 applications';
run;
proc freq data=have;
     tables id_project;
     format id_project custf;
run;

Re: Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 14:44:30 GMT

Thank you for your answer. I didn't now those procedures. But when we use proc format, aren't we subtituting the values of 1,2,3... on our table with the string ? Because I don't have such values

Re: Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 14:52:55 GMT

I tried the code and it returns the number of applications for each project. But I want the number of projects that have 1,2,3... applications. Its like 4 rows and 2 columns for the output.

Re: Summary functions are restricted to the select and having clauses only

PaigeMiller — Thu, 15 Apr 2021 15:04:00 GMT

Please show us example of your input data. Please show us the desired output.

Re: Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 15:16:51 GMT

Its a very confidential dataset but I made an exemple table that looks pretty similar with the required columns.

Table :

Output:

	Nbr Projects
Projects with 1 application	xx
Projects with 2 applications	xx
Projects with 3 applications	xx
Projects with >3 applications	xx

Re: Summary functions are restricted to the select and having clauses only

PaigeMiller — Thu, 15 Apr 2021 15:30:05 GMT

So project 1000 has 8 applications and project 5000 has 2 applications? I think using PROC FREQ twice gets you what you want.

proc format;
    value custf 1='Projects with 1 application'
         2='Projects with 2 applications'
         3='Projects with 3 applications'
         4-high='Projects with >3 applications';
run;
proc freq data=have;
     tables id_project/noprint out=applications;
run;
proc freq data=applications;
     tables count;
     format count custf.;
run;

Re: Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 15:35:30 GMT

Exactly ! I tried the code. It tells me that the count variable is not found.

PS: And in the data (data = ) variable I put the name of the dataset (test2) right ?

Re: Summary functions are restricted to the select and having clauses only

Adir92 — Thu, 15 Apr 2021 15:39:57 GMT

Its working ! Thanks you so much !

Re: Summary functions are restricted to the select and having clauses only

PaigeMiller — Thu, 15 Apr 2021 15:42:58 GMT

Good news!