BookmarkSubscribeRSS Feed
JimLittle
Fluorite | Level 6

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.

5 REPLIES 5
MichelleHomes
Meteorite | Level 14

Hi

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#n0bk4cm9klgbh3n1jxpb8...

Kind Regards,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
JimLittle
Fluorite | Level 6

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.

I_Kong_SAS
SAS Employee

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.


case_statement.png

Thanks again,

I-Kong

JimLittle
Fluorite | Level 6

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.

bagalaty
Calcite | Level 5

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 ? 

value desc.png

 

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';

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 5 replies
  • 7188 views
  • 1 like
  • 4 in conversation