topic The sum does not work? in SAS Programming

The sum does not work?

ybz12003 — Wed, 04 Jan 2023 17:08:32 GMT

Hello,

I created a sum variable to calculate the sum of three columns; I found out that the function didn't work in the missing value. Please help; thanks.

data Cost;  
	Format IDs $10. ED DOLLAR15.2 ICU DOLLAR15.2 Lab DOLLAR15.2; 
	inFormat IDs $10. ED ICU Lab comma15.; 
	infile datalines delimiter='/'; 
	input IDs ED ICU Lab;  
	datalines;                     
EC1R00002/ 899.28/ / 256.31/
KC1Y00012/ / 11,080.82/ 619.91/
WC1G00013/ 332.34/ 929.71/ 2,066.98/
;  

data Cost_Sum;
	set Cost;
	sum=ED+ICU+Lab;
run;

Re: The sum does not work?

PaigeMiller — Wed, 04 Jan 2023 17:16:30 GMT

Maxim 2

READ THE LOG

1130  data Cost_Sum;
1131      set Cost;
1132      sum=ED+ICU+Lab;
1133  run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2 at 1132:11
NOTE: There were 3 observations read from the data set WORK.COST.
NOTE: The data set WORK.COST_SUM has 3 observations and 5 variables.
NOTE: Compressing data set WORK.COST_SUM increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

You can't add missing values to a non-missing. The result is a missing value. The solution is to use the SUM function, which ignores missing values.

sum=sum(ED,ICU,Lab);

Re: The sum does not work?

ybz12003 — Wed, 04 Jan 2023 17:17:48 GMT

Awesome, thanks.

Re: The sum does not work?

FreelanceReinh — Wed, 04 Jan 2023 19:40:19 GMT

Hello @ybz12003,

I would add the ROUND function to PaigeMiller's solution:

sum=round(sum(ED,ICU,Lab),0.01);

(or with a suitable smaller rounding unit than 0.01 if any of the ED, ICU or Lab values could have more than two decimals).

The reason is that otherwise (!) tiny rounding errors are likely to occur, which can cause unwanted surprises later on. Your third example is a case in point. Using Windows SAS 9.4M5, the ROUND function makes a difference:

data _null_;
input ED ICU Lab :comma.;
sum_old = sum(ED,ICU,Lab);
sum_new = round(sum(ED,ICU,Lab),0.01);
put (sum:) (= best32. /);
if sum_old ne sum_new then put / 'Surprised?' /;
put (sum:) (= hex16. /);
cards;
332.34 929.71 2,066.98
;

Log:
sum_old=3329.03
sum_new=3329.03

Surprised?

sum_old=40AA020F5C28F5C2
sum_new=40AA020F5C28F5C3

Note that the HEX16. representation of sum_new is correctly rounded up: ...F5C28F5C28F5C28... → ...F5C3 (repeating 5-digit pattern in a hexadecimal periodic fraction), whereas in sum_old it is rounded down, making it unequal to 3329.03.