topic Convert a string to a category in SAS Visual Analytics

Convert a string to a category

Coop — Mon, 17 Apr 2017 17:21:27 GMT

Hello. I am using SAS Visual Analytics 7.3 and I am currently using multiple if-then-else statements along with parameter values to return the appropriate category in a bar chart. The syntax I am using is like this:

IF ( ( 'ATD Cohort Year'p = '2012' ) AND ( 'End Term'p = '201214' ) )
RETURN 'ATD_2012_2014'n
ELSE IF ( ( 'ATD Cohort Year'p = '2012' ) AND ( 'End Term'p = '201216' ) )
RETURN 'ATD_2012_2016'n
ELSE ' '

The issue is that there are many more combinations of "Cohort Year" and "End Term" than what I showed in the code above and I would like to simplify my code. I am hopeful that I can use the concatenation operator to write a single if-then-else statement but I cannot figure out a way to make SAS VA interpret the string as a category.

The following code block creates the string "ATD_201212_201214" (when using a parameter value of 2012 for ATD Cohort Year and 201214 for End Term), which is the name of the category I want to display. SAS VA does not interpret this string as a category, however, and nothing is returned.

IF ( ( 'ATD Cohort Year'p NotMissing ) AND ( 'End Term'p NotMissing ) )
RETURN Concatenate(Concatenate('ATD_', Concatenate('ATD Cohort Year'p, '12_')), 'End Term'p)
ELSE ' '

The code attempt below was to make the concatenations "look" like 'ATD_2012_2014'n (when using a parameter value of 2012 for ATD Cohort Year and 201214 for End Term) in another attempt to get SAS VA to interpret the string as a category. The backslashes are used to escape the single quote inside the pair of single quotes.

IF ( ( 'ATD Cohort Year'p NotMissing ) AND ( 'End Term'p NotMissing ) )
RETURN Concatenate(Concatenate(Concatenate('\'ATD_', Concatenate('ATD Cohort Year'p, '12_')), 'End Term'p),'\'n')
ELSE ' '

Is there a way to cobble together strings to create a calculated category in SAS VA? If not, is there another way to achieve the result that I want without writing dozens of if-then-else statements?

Thanks so much.

Re: Convert a string to a category

Sam_SAS — Mon, 17 Apr 2017 19:46:50 GMT

Hi Coop,

I'm confused as to why your second expression doesn't work:

IF ( ( 'ATD Cohort Year'p NotMissing ) AND ( 'End Term'p NotMissing ) )
RETURN Concatenate(Concatenate('ATD_', Concatenate('ATD Cohort Year'p, '12_')), 'End Term'p)
ELSE ' '

You say that it doesn't return a category. Is it possible one of your parameters is numeric instead of character?

Thanks,

Sam

Re: Convert a string to a category

Coop — Mon, 17 Apr 2017 20:04:34 GMT

Hello Sam_SAS. Thanks so much for responding. Both of the parameters are character. The concatenation of the parameters produces the correct string (ATD_201212_201214) but I believe that a string is all that SAS sees. When I view the preview of the calculated item, each row has the string ATD_201212_201214 instead of the contents of the ATD_201212_201214 that is already in the data set.

Re: Convert a string to a category

Sam_SAS — Mon, 17 Apr 2017 20:22:30 GMT

Ohhhh now I get it, you want to return the value of a different category, depending on the conditions. Now your third expression makes sense.

I tried a very simple proof of concept here:

RETURN Concatenate('Ag', 'e\'n')

where Age is a variable in my table, and i can't seem to get it to evaluate the value of Age instead of just returning "Age'n".

I suspect this just cannot work.

The good(?) news is, creating all of the individual conditions should work if you can stand the tedium to build the expression. You could make a script in Perl or whatever to generate the syntax and then just paste it into the VA expression builder, if that helps.

Hope that helps,

Sam

Re: Convert a string to a category

Coop — Mon, 17 Apr 2017 20:26:10 GMT

Awww, sad. Thanks for taking a look. Making all the individual conditions isn't great but I thought it would be easier if I could make things more efficient. After all, this probably won't be the last time I run into a situation like this. I haven't thought of using another program to create the conditions. Using a script may cut down on the chance of a typing error. Thanks again.

Re: Convert a string to a category

Sam_SAS — Mon, 17 Apr 2017 21:14:30 GMT

I'll confirm with the developers whether or not it is possible to specify a variable in this way. If it is not, we should create an "idea" (effectively, a feature request) for this.

Thanks,

Sam

Re: Convert a string to a category

Coop — Tue, 18 Apr 2017 12:26:01 GMT

Excellent. Thanks so much.

Re: Convert a string to a category

KeithM — Tue, 18 Apr 2017 14:30:23 GMT

Another approache might be to restructure your data. So instead of having the additional measures, you simply have one measure that represents all of the value. You would then have a separate column that is the name of the string you are looking for (MyNewString). So, you would build your bar chart with the same measure all of the time, but you would filter that measure based on the character value of another column. You would then add a filter to the data similar to your other expression:

IF ( ( 'ATD Cohort Year'p NotMissing ) AND ( 'End Term'p NotMissing ) )
RETURN "MyNewString"n = Concatenate(Concatenate('ATD_', Concatenate('ATD Cohort Year'p, '12_')), 'End Term'p)
ELSE 1 = 0

Re: Convert a string to a category

Sam_SAS — Tue, 18 Apr 2017 17:22:51 GMT

I confirmed with the developers that it is not possible currently. I think to make it work they might need to create a new operator specifically for the purpose, but that is not out of the question.

Can you go here:
https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas/label-name/sas%20visual%20analytics

and create an "idea"?

What you would label such an idea is sort of tricky. Something like, "Enable dynamic variable references in expression syntax"?

Thanks,
Sam

Re: Convert a string to a category

Coop — Tue, 18 Apr 2017 18:39:13 GMT

Thanks for your thoughts Keith. It would take quite a bit of work to restructure my current dataset but that is something to think about with new datasets.

Re: Convert a string to a category

Coop — Tue, 18 Apr 2017 18:53:42 GMT

Thanks Sam. I did create an "idea" as you suggested and I used the idea name that you suggested. I think this feature would be helpful for many. Have a great day.