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

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

 

Picture1.png

1 ACCEPTED SOLUTION

Accepted Solutions
HunterT_SAS
SAS Employee

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. 

View solution in original post

4 REPLIES 4
acordes
Rhodochrosite | Level 12

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
Super User

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.

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.
HunterT_SAS
SAS Employee

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
Fluorite | Level 6

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.

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
  • 4 replies
  • 832 views
  • 2 likes
  • 4 in conversation