BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Genesis007
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stu_SAS
SAS Employee

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!

View solution in original post

5 REPLIES 5
Stu_SAS
SAS Employee

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!

Genesis007
Obsidian | Level 7
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.
Stu_SAS
SAS Employee

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.

Genesis007
Obsidian | Level 7
Thanks for this tips.
I'll try it and I'll keep you informed if it's OK.
😉
Quentin
Super User

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

?

The Boston Area SAS Users Group is hosting free webinars!
Next up: Joe Madden & Joseph Henry present Putting Power into the Hands of the Programmer with SAS Viya Workbench on Wednesday Nov 6.
Register now at https://www.basug.org/events.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 5 replies
  • 4036 views
  • 3 likes
  • 3 in conversation