topic Re: Calculation with Null values in SAS Visual Analytics

Calculation with Null values

Genesis007 — Tue, 09 Aug 2022 15:10:10 GMT

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 ?

Thanks in advance.

Michel.

Re: Calculation with Null values

Stu_SAS — Tue, 09 Aug 2022 15:24:58 GMT

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!

Re: Calculation with Null values

Genesis007 — Tue, 09 Aug 2022 15:47:13 GMT

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.

Re: Calculation with Null values

Stu_SAS — Tue, 09 Aug 2022 16:59:18 GMT

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.

Re: Calculation with Null values

Quentin — Tue, 09 Aug 2022 17:21:01 GMT

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);

Re: Calculation with Null values

Genesis007 — Wed, 10 Aug 2022 16:16:15 GMT

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