SAS Viya 4 - Visual Analytics Expression Builder - Replace Operator limitation/bug

[dylanleong78]

Hi,

I was attempting to use a text box to enable users to enter list of multiple values for filtering based on the excellent article below:

https://communities.sas.com/t5/SAS-Communities-Library/Using-Text-Box-to-enter-list-of-values-for-fi...

There was a slight difference because users were copying values from an Excel spreadsheet and pasting into the text box to filter. And this could possibly contain hundreds of different values. When copying and pasting from Excel, the values pasted are usually separated by a blank space. Therefore, in order for me to mimic the solution in the article above, I attempted to use the Replace operator as per below to replace empty blank space with a comma.

https://documentation.sas.com/doc/en/vacdc/8.3/vareportdata/p0dovqqp325peyn1dpijpxxmwj83.htm

Original from Excel:

1234567 7654321 1987654

After applying: Replace('myparam'p, ' ', ',', _ALL_)

1234567,7654321,1987654

This worked pretty well and users were able to filter based on list of values copied and pasted from Excel. However, a user complained that the report was not showing all the expected results. He had 183 different values  but the report was only showing about 25 of those values. Upon further investigation, I discovered that when the expression builder applied the Replace() function against the parameter value, the result was limited to 200 characters. Therefore I think that there is either a bug or a limitation in the expression builder, specifically in the Replace() function. I had Upcase, RemoveBlanks and Concatenate applied against the parameter and they did not alter the length of the parameter.

I managed to produce a workaround that does not use Replace(). However, this workaround has its limitations. I needed the commas between the values to ensure the filter only return exact matches. However, without the commas, the filter was returning missing values, 0 and was returning partial matches.

1. Can SAS raise a bug and fix this

2. Does anyone else have a savvy workaround?

[HunterT_SAS · Accepted solution]

First, there is an existing defect for this Replace limitation so I've added this post to that. Unfortunately no ETA on when it might be addressed but confirming a defect exists. 

Second, I would never call this savvy or elegant because it looks pretty awful in the Visual mode of calculated items, BUT one workaround would be something like this:

IF ( GetLength('MyParameter'p) > 200 )
RETURN Concatenate(Replace(Substring('MyParameter'p, 1, 200), ' '
, ',', _All_), Replace(Substring('MyParameter'p, 201, GetLength(
'MyParameter'p)), ' ', ',', _All_))
ELSE Replace('MyParameter'p, ' ', ',', _All_)

where the idea would be check the length first, if it's more than 200 then split it using substring and replace 200 characters at a time, then concatenate back together. In this example it's assuming it would never be more than 400 characters so you might need to account for that. In my very limited testing though it did the job for something that had 252 characters. 

[dylanleong78]

Thanks HunterT,

Your suggestion works and I was able to prevent partial matches and 0 but somehow fails to filter out missing values when the >200 character logic is applied. I will accept this solution. Thanks for your help!

You are also right that it isn't the prettiest piece of logic, whether you are looking at it from a coding perspective or in visual mode. But that applies to the expression builder in general whichever way you see it. Absolutely hate that it reformats my written logic into incomprehensible lines of jibberish. It is bad enough that logic has to be written in "business-friendly" language but to lose all my indentation and formatting is taking it to a whole new level. Having said that, I highly doubt that there is sufficient appetite to overhaul the expression builder.

IF ( 'pid_list5'p IsSet ) 

RETURN ( 

IF ( GetLength('pid_list5'p) > 200 ) 

RETURN ( RemoveBlanks(Concatenate(Concatenate(Concatenate( 

Concatenate(Concatenate(Concatenate(',', RemoveBlanks(Replace( 

Substring('pid_list5'p, 1, 200), ' ', ',', _All_), _All_)), 

Concatenate(',', RemoveBlanks(Replace(Substring('pid_list5'p, 201 

, 400), ' ', ',', _All_), _All_))), Concatenate(Concatenate(',', 

RemoveBlanks(Replace(Substring('pid_list5'p, 401, 600), ' ', ',', 

_All_), _All_)), Concatenate(',', RemoveBlanks(Replace(Substring( 

'pid_list5'p, 601, 800), ' ', ',', _All_), _All_)))), Concatenate 

(Concatenate(',', RemoveBlanks(Replace(Substring('pid_list5'p, 

801, 1000), ' ', ',', _All_), _All_)), Concatenate(',', 

RemoveBlanks(Replace(Substring('pid_list5'p, 1001, 1200), ' ', 

',', _All_), _All_)))), Concatenate(Concatenate(',', RemoveBlanks 

(Replace(Substring('pid_list5'p, 1201, 1400), ' ', ',', _All_), 

_All_)), Concatenate(',', RemoveBlanks(Replace(Substring( 

'pid_list5'p, 1401, 1600), ' ', ',', _All_), _All_)))), 

Concatenate(',', RemoveBlanks(Replace(Substring('pid_list5'p, 

1601, GetLength('pid_list5'p)), ' ', ',', _All_), _All_))), _All_ 

) Contains Concatenate(',', Concatenate(UpCase(RemoveBlanks( 

'PRODUCT_ID'n, _All_)), ',')) ) 

ELSE ( Concatenate(',', Concatenate(RemoveBlanks(Replace( 

'pid_list5'p, ' ', ',', _All_), _All_), '_All_')) Contains 

Concatenate(',', Concatenate(UpCase(RemoveBlanks('PRODUCT_ID'n, 

_All_)), ',')) ) ) 

ELSE ( 1 = 0 )