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.
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 |
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.
Thank you but the Group By value (Unit_Cap_Cyl) is not a calculated value.
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.