topic Re: Decimal/percent calculations not working in SAS Programming

Decimal/percent calculations not working

thelowendHz — Mon, 16 May 2022 20:18:57 GMT

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?

Re: Decimal/percent calculations not working

PaigeMiller — Mon, 16 May 2022 21:52:04 GMT

It works if you take the GROUP BY and ORDER BY out.

Re: Decimal/percent calculations not working

thelowendHz — Tue, 17 May 2022 12:42:50 GMT

Hi Paige,

I tried taking those out and I got the same results. Any other suggestions?

Thanks,

Ryan

Re: Decimal/percent calculations not working

PaigeMiller — Tue, 17 May 2022 12:49:22 GMT

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.

Re: Decimal/percent calculations not working

thelowendHz — Tue, 17 May 2022 14:06:51 GMT

This is what it looks like:

column_a	column_b
.	1
.	1
1	1
.	1
.	1
.	1
1	1
.	1
.	1

Re: Decimal/percent calculations not working

Kurt_Bremser — Tue, 17 May 2022 14:23:51 GMT

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%

Re: Decimal/percent calculations not working

Tom — Tue, 17 May 2022 14:25:45 GMT

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;