Trying to understand a data filter with empty 'in' comparator within an IF statement

[JeremyAT]

Dear SAS VA Gurus,

I hope someone can help me with this. I'm relatively new to SAS and I need to edit a report developed by a colleague that has since left my organisation. This report contains a data filter that makes use of empty 'in' comparators within an IF statement. These empty 'in' comparators are also used in several calculated items throughout the report. I cannot for the life of me wrap my head around what these do. Could someone please explain it to me?

IF ( 'Stocktake Rolling Year'p In () )
RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR (
'Rolling Stocktake Timetable'n In ('Year One') ) )
ELSE (
IF ( 'Stocktake Rolling Year'p In () )
RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR (
'Rolling Stocktake Timetable'n In ('Year Two') ) )
ELSE (
IF ( 'Stocktake Rolling Year'p In () )
RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR (
'Rolling Stocktake Timetable'n In ('Year Three') ) )
ELSE ( 'Main Asset Number'n Missing ) ) )

[acordes]

Hi,

the IN operator together with parameters can unload its values, I think this happened to you. 

It's better practice in this case to replace it with hard-coded "=", and in case several values pass through the filter, combine them wit OR operator. 

Basically the filter resolves to true or false, have this in mind. The ending statement could be trivial as well, i.e. 1=1 to ensure true condition.  

[Quentin]

I'm not familiar with this language.  I guess it's some sort of report-builder language in Visual Analytics?  I tried matching parentheses to see if possible logic would become clearer, but it didn't really help.

IF ( 'Stocktake Rolling Year'p In () )
  RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year One') ) )
ELSE (
      IF ( 'Stocktake Rolling Year'p In () )
        RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year Two') ) )
      ELSE (
             IF ( 'Stocktake Rolling Year'p In () )
               RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year Three') ) )
             ELSE ( 'Main Asset Number'n Missing ) 
            ) 
      )

This sort of weird nesting of IF/ELSE suggests this language doesn't haven an ELSE IF statement?  Maybe a user is expected to input a year value for each statement that currently has an empty IN().  So they could change it to:

IF ( 'Stocktake Rolling Year'p In (2023) )
  RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year One') ) )
ELSE (
      IF ( 'Stocktake Rolling Year'p In (2022) )
        RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year Two') ) )
      ELSE (
             IF ( 'Stocktake Rolling Year'p In (2021) )
               RETURN ( ( 'Controlled Item Indicator'n In ('YES') ) OR ('Rolling Stocktake Timetable'n In ('Year Three') ) )
             ELSE ( 'Main Asset Number'n Missing ) 
            ) 
      )

But that's just a wild guess.

[HunterT_SAS · Accepted solution]

Actually I think this might be a defect in Visual Analytics. What version of Visual Analytics is this? I'm assuming 8.5.2 on Viya 3.5.

In short what you have here is an IN function that uses a Visual Analytics Parameter, as opposed to a regular data item. A parameter is a special type of data item you create inside of Visual Analytics that can be assigned to prompts (drop down list, etc) so that the value of the prompt is assigned to that parameter, and then used in calculated items and filters. 

This parameter is being used in an IN function such that if the parameter = any of the IN values then it proceeds to the next point....except obviously in your case the IN filters are all blank.

An IN filter with a parameter this way (not blank but with values) all should work just fine though, which is why I think this is a bug. If I create the exact same thing in Visual Analytics 8.5.2, I run into the same problem. Create my calculated item or filter with the parameter, go back later and edit it to find it is blank. The same actions work correctly in versions 7.51 and Viya 4, at least so far in my testing.

So if I had to guess, I'd say your original report developer did this in version 7.5 to start with and it worked great, then you moved to Viya 3.5 and VA 8.5.2 and found this problem. 

I don't have a great answer here. If you know what Years are supposed to be set here then what @acordes suggested would be best...rather than use an IN function, split it up into = and OR functions, like:

('Stocktake Rolling Year'p = 2023) OR ('Stocktake Rolling Year'p = 2022) OR .....

but then you'd have to do that for every report that is impacted by this. 

Otherwise I think your best bet at this point would be to get a technical support track open so we can investigate:

https://support.sas.com/en/technical-support/submit-a-support-request.lang.html

but unfortunately I suspect that no matter what the outcome is there (a fix , or alternative workaround suggested), in the end you are still going to have to manually update these reports at this point to correct the filters. 

[JeremyAT]

Hi Hunter_SAS,

I experimented with the filter and was able to confirm what I think you've suggested. In this case, the available values for the parameter 'Stocktake Rolling Year' are controlled by another parameter (Year) and calculated item (Rolling Stocktake Schedule).  I found that the "in" comparators were blank as only one of the three values the parameter could take were available at any given time, depending on the value of the 'Year' parameter. If I change the comparator to '=' then this issue goes away 😄

Thanks so much for your help.