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;
.
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);
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);
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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.