PROC SQL error

[pchappus]

I'm getting an error of Summary functions are restricted to the SELECT and HAVING ...

Is this because I can't group by a variable in the same statement that I create it in?

PROC SQL;
	CREATE TABLE work.counts AS
		SELECT
			Report_Month,
			CASE 
				WHEN P = 'ACC' THEN 'A'
				WHEN P = 'DCL' AND S = 'ACC' THEN 'B'
				WHEN P = 'DLC' AND S = 'DLC' AND SS = 'ACC' THEN 'C'
				WHEN SS = 'DLC' THEN 'E'
			END AS Risk,
			COUNT(DISTINCT SSN) AS COUNT
		FROM work.Master
		GROUP BY 
			Report_Month,
			Risk,
			Count;
QUIT;

[Astounding]

Why not just remove COUNT from the GROUP BY clause?  You're already getting one observation per REPORT_MONTH / RISK group ... COUNT doesn't need to be a grouping variable.

[Reeza]

Put the word 'calculated' in front of any calculated variables when using it later.

[novinosrin]

You have a logical error. My i request sample data of what you have and want plz

[ShiroAmada · Accepted solution]

PROC SQL;
	CREATE TABLE work.counts AS
		SELECT
			Report_Month,
			CASE 
				WHEN P = 'ACC' THEN 'A'
				WHEN P = 'DCL' AND S = 'ACC' THEN 'B'
				WHEN P = 'DLC' AND S = 'DLC' AND SS = 'ACC' THEN 'C'
				WHEN SS = 'DLC' THEN 'E'
			END AS Risk,
			COUNT(DISTINCT SSN) AS COUNT
		FROM work.Master
		GROUP BY 
			Report_Month,
			calculated Risk;
QUIT;

I removed COUNT from the group by.  I added the keyword calculated before the variable RISK.  SAS needs an identifying keyword when you use a derived column/field/variable when it is built and used within the same query.