topic Using IF statements in Query Builder in SAS Visual Analytics

Using IF statements in Query Builder

JimLittle — Tue, 13 Jan 2015 23:00:20 GMT

The query builder allows the creation of custom expressions that use basic arithmetic.

However, it seems that it is impossible to use any sort of Boolean Logic statements (VA 6.4)

This is really, really annoying and value and purpose of the Query Building feature.

Please advise whether this has been resolved in later versions.

Re: Using IF statements in Query Builder

MichelleHomes — Wed, 14 Jan 2015 07:15:23 GMT

Sorry to hear of your frustrations. The SAS Visual Analytics Query Builder generates SQL code and as such if statements are not supported with this ANSI standard. If you are wanting to do Boolean logic, have a look at the CASE statement that can be used in a column expression, http://support.sas.com/documentation/cdl/en/vaug/67270/HTML/default/viewer.htm#n0bk4cm9klgbh3n1jxpb8lfxu12p.htm

Kind Regards,

Michelle

Re: Using IF statements in Query Builder

JimLittle — Wed, 14 Jan 2015 20:09:28 GMT

Hi Michelle,

Yes, a CASE statement would work well. However, I cannot find any example of how to use the statement in any of the documentation for VA, and the link that you send through does not help (aside from "Enter arithmetic operators and expressions such as CASE statements directly in the SQL expression area").

But please explain why the functionality within the Query tool in terms of expression building screens is so deficient???

VA is promoted as a tool that requires no knowledge of SQL, and while the expression builders within VA Explorer/Reporter are great - the functionality of the Query tool is woefully deficient.

The documentation I could find on the CASE syntax was here: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm

I could not find anything related to CASE and VA specifcally.

James.

Re: Using IF statements in Query Builder

I_Kong_SAS — Fri, 16 Jan 2015 15:23:40 GMT

Hi James, thank you for your feedback and sorry to hear of your frustration. We agree it makes sense to add additional functions and UI for this kind of logic. In the meantime CASE and other expressions from the doc or copied from your exploration or report expressions can be typed or pasted into the Data Builder expression area. A simple example is shown in the screenshot below. We will also pass along your feedback to our documentation team.

Thanks again,

I-Kong

Re: Using IF statements in Query Builder

JimLittle — Sun, 18 Jan 2015 20:30:33 GMT

Thanks for that I-Kong,

I have had some success with it now - which has been handy.

I have discovered some bad performance issues if you try to create calculated items on 165million rows of data, so I had to do some of the initial logic in Query Builder to filter out the data and perform the basic logic.

Thanks for passing this do the documentation team, the Query builder is definately not well covered compared to Explorer and Reporter.

James.

Re: Using IF statements in Query Builder

bagalaty — Wed, 14 Dec 2016 15:01:27 GMT

What if the lookups is too big and changable , then i cant make it constant condition,

for example the CASEMGMT.REF_TABLE_VALUE contains parents and childs lookups like attached image , then how to select the description automatically ?

It's hard to code it to case X_RT_ECONOMIC_CD ="4_203" then 'some text description here'

Can i write innser select inside VA expression like below :

define X_KYC_ECONOMIC_CODE='2_0';
select VALUE_DESC from CASEMGMT.REF_TABLE_VALUE where REF_TABLE_NM='X_RT_ECONOMIC_CD1' AND VALUE_CD='&X_KYC_ECONOMIC_CODE';