I have a simple query where I'm summarizing some numerical fields:
PROC SQL;
CREATE TABLE test AS
SELECT
SUM(column_a) AS column_a,
SUM(column_b) AS column_b,
SUM(column_a)/SUM(column_b) as Rate format=percent8.2
FROM
table1
GROUP BY
1
ORDER BY
1
;quit;
This is an example of the results:
column_a | column_b | Rate |
8 | 99 | 0.00% |
Why aren't my percentage calculations working?
It works if you take the GROUP BY and ORDER BY out.
Hi Paige,
I tried taking those out and I got the same results. Any other suggestions?
Thanks,
Ryan
Please provide a portion of the data set named TEST.
Provide them according to these instructions or by typing working SAS data step code yourself.
This is what it looks like:
column_a | column_b |
. | 1 |
. | 1 |
1 | 1 |
. | 1 |
. | 1 |
. | 1 |
1 | 1 |
. | 1 |
. | 1 |
This is how you present data:
data have;
infile datalines dlm="09"x dsd;
input column_a column_b;
datalines;
. 1
. 1
1 1
. 1
. 1
. 1
1 1
. 1
. 1
;
and this is what you get when you run the code without GROUP BY:
proc sql;
create table want as
select
sum(column_a) as column_a,
sum(column_b) as column_b,
sum(column_a) / sum(column_b) as Rate format=percent8.2
from have
;
quit;
proc print data=want noobs;
run;
Result:
column_a column_b Rate 2 9 22.22%
Your code cannot use column 1 as the grouping variable. It is the result of an aggregate function.
Perhaps you meant there was some other variable you want to group by?
Let's add a variable ID to your example data and try that.
data have;
id=1;
column_b=1;
input column_a @@;
cards;
. . 1 . . . 1 . .
;
PROC SQL;
CREATE TABLE test AS
SELECT ID
, SUM(column_a) AS column_a
, SUM(column_b) AS column_b
, SUM(column_a)/SUM(column_b) as Rate format=percent8.2
FROM
have
GROUP BY
1
ORDER BY
1
;
quit;
proc print;
run;
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.