topic Re: PROC SQL error in SAS Programming

PROC SQL error

pchappus — Mon, 03 Dec 2018 21:00:30 GMT

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;

Re: PROC SQL error

Astounding — Mon, 03 Dec 2018 21:13:50 GMT

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.

Re: PROC SQL error

Reeza — Mon, 03 Dec 2018 21:37:55 GMT

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

Re: PROC SQL error

novinosrin — Mon, 03 Dec 2018 21:47:43 GMT

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

Re: PROC SQL error

ShiroAmada — Tue, 04 Dec 2018 02:53:46 GMT

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.