Proc SQL Error with Summary functions are restricted tot he SELECT and HAVING clauses

[lchristensen]

I am taking a Udemy class for preparation for the Advanced SAS Programmer certificate. I've never used PROC SQL before and working my way thru the class material using a copy of some useful data from work. I am running SAS V9.4 but have an older set of the Base SAS Procedures Guide (V9.1.3).

Source:

Proc SQL;
	title  'Sum of defined cylinders by volume emulation type';
	select  Unit_Cap_Cyl,
			count(Unit_Cap_Cyl) as 	Qty_Vols			label='# Volumes'	,
			sum(Unit_Cap_Cyl) 	as 	Sum_Unit_Cap_Cyl	label='# Cylinders'

	from	Work.DSPL_TBL
	group by Sum_Unit_Cap_Cyl;	
	quit;

If I comment out the 'group by Sum_Unit_Cap_Cyl' statement and put the semicolon after the from statement I will get a table with the correct data but not sorted.

If I use the group by statement I get the 'Summary functions are restricted to the SELECT and HAVING clauses only' error. The error shows up after the group by statement.

I'm looking at the example code on Page-1149 of the Base SAS V9.1.3 Procedures Guide (volume-3) and I think functionally my code should match the example there.

Once I have this piece working I will want to produce only one line for each unique value of the Unit_Cap_Cyl variable.

There are many previously submitted questions out here with the same error but I'm not seeing an answer that seems to fit.

[ChrisHemedinger]

In PROC SQL you need to tell the Group By that you are using a calculated column. And I think you want ORDER BY for sorting.

Proc SQL;
	title  'Sum of defined cylinders by volume emulation type';
	select  Unit_Cap_Cyl,
			count(Unit_Cap_Cyl) as 	Qty_Vols			label='# Volumes'	,
			sum(Unit_Cap_Cyl) 	as 	Sum_Unit_Cap_Cyl	label='# Cylinders'

	from	Work.DSPL_TBL
	order by (calculated Sum_Unit_Cap_Cyl);	
	quit;

All of the most recent SAS documentation is available for free online.

Check out this recent tutorial that covers this topic here.

[lchristensen]

Thank you but the Group By value (Unit_Cap_Cyl) is not a calculated value.

[lchristensen · Accepted solution]

I got it to work.

The GROUP BY Unit_Cap_Cyl needed   'having Unit_Cap_Cyl GE 0'     which also got rid of a lot of missing values.

Proc SQL;
	title  'Sum of defined cylinders by volume emulation type';
	select  Unit_Cap_Cyl,
			count(Unit_Cap_Cyl) as 	Qty_Vols			label='# Volumes'	,
			sum(Unit_Cap_Cyl) 	as 	Sum_Unit_Cap_Cyl	label='# Cylinders'
	from	Work.DSPL_TBL
		GROUP BY Unit_Cap_Cyl	having Unit_Cap_Cyl GE 0
		order by Unit_Cap_Cyl;
	quit;

Sum of defined cylinders by volume emulation type

Unit_Cap_Cyl	Volumes	Cylinders
2107	32	67424
3339	576	1923264
5800	256	1484800
10017	2688	26925696
32760	160	5241600
65520	5561	3.6436E8
262668	80	21013440
263781	768	2.0258E8