Obsidian | Level 7

## Calculation with Null values

Hi Everybody,

First of all, I'm using SAS Enterprise Guide V8.3.0.103, SAS Visual Analytics V8.5.2 and SAS VIYA V03.05.

I've prepared my data in Enterprise guide.
I've made a left join between two tables, and, logically, I've got some data with no correspondance and it's make a null value.

For example, in my Table 1 I've an ordered quantity (A),  a delivered quantity (B), and in my Table 2 I've got a reserved quantities (C).

Of course, because of I haven't a reserved quantity for each order, the reserverd quantity may be equal to NULL.

I want to create a calculated element (R), the formula is :

R = A-B-C

R = 4-2-NULL => Result is "." and I would like to have 2

If C is not null (or missing) my result is correct. If C is null (or missing), I have no result !!!

What kind of solution may I use to have C = 0 when C is missing or NULL ?

Michel.

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Employee

## Re: Calculation with Null values

Hey @Genesis007! One unique feature of Visual Analytics is the ability to embed if/else statements directly within calculations. You can add your code to convert missing values to 0's in a single line:

`A - B - ( if('C'n = .) return 0 else 'C'n )`

Hope this helps!

5 REPLIES 5
SAS Employee

## Re: Calculation with Null values

Hey @Genesis007! One unique feature of Visual Analytics is the ability to embed if/else statements directly within calculations. You can add your code to convert missing values to 0's in a single line:

`A - B - ( if('C'n = .) return 0 else 'C'n )`

Hope this helps!

Obsidian | Level 7

## Re: Calculation with Null values

Thank you so much Stu. It's working perfectly,
I would like to know if there is a solution to automate the transformation of missing numerical values into 0, without writing an if/else statement ?
It works, and thanks again, but I think it's a little bit difficult and repetitive for a non "power user" guy.
Michel.
SAS Employee

## Re: Calculation with Null values

Unfortunately there is not at this time with the exception of doing the data prep beforehand. For example, before loading your data into CAS/LASR, you can loop through an array in a DATA Step to set any missing values of specific variables to 0:

``````data foo;
set bar;
array var[*] var1 var2 var3;

do i = 1 to dim(var);
if(var[i] = .) then var[i] = 0;
end;

drop i;
run;``````

I would recommend posting this request in the SASWare Ballot as an idea. Thank you for your feedback on this - I have noted it down.

Obsidian | Level 7

## Re: Calculation with Null values

Thanks for this tips.
I'll try it and I'll keep you informed if it's OK.
😉
PROC Star

## Re: Calculation with Null values

I don't have Viya, but it looks like there is a SUM function that ignores missings, like base SAS.  So maybe you could do:

``R=sum(A,-B,-C);``

?

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Discussion stats
• 5 replies
• 1915 views
• 3 likes
• 3 in conversation