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.
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!
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!
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.
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);
?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.