Summary functions are restricted to the select and having clauses only

[Adir92]

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 !

[PaigeMiller]

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;

[Adir92]

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

[Adir92]

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.

[PaigeMiller]

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

[Adir92]

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

[PaigeMiller · Accepted solution]

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;

[Adir92]

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 ?

[Adir92]

Its working ! Thanks you so much !

[PaigeMiller]

Good news!