SAS VA - how to create calculations using columns / data in a List Table

[MikeBsocal]

Hi All

First time poster here and I'm new to SAS Visual Analytics - however I'm an advanced user in Tableau, Power BI, and a few other similar tools.

I'm trying to do a simple table calculation -- where I take a number in one column of a List Table and add it to the number in another column in that List Table. In the original data, those two numbers do not exist in the same row -- they are only in the same row in the List Table. So I'm guessing I need some kind of Table-based calculation/function.

This is what the overall layout looks like. I have a single data source with info about drugs. In the left List Table, I have a list of drugs used for CHF. In the middle List Table, I have a list of drugs used for Heart Failure. In the right List Table, I want to show the drugs common to both CHF and Heart Failure, based on their NDC11 (this is the unique identifier for each drug). I've created flags for both the Left and Right drug markets, 1 and 2 respectively.

I want to be able to add the 1 and 2 in a new column -- from which I can create a filter for "3". Thus showing us only the drugs in common to both tables.

In this screenshot below, I've included a column for Market. Market comes from the source data and is the dimension that splits the same drug/NDC11 into two rows. That is, they're using the same exact drug for two different markets. As a result of this split, I'm unable to simply add 1 and 2 together at the detail level.

Any solutions or tips are greatly appreciated! In the end I just want to list which drugs are common to both markets. I don't care how it gets done. Thanks

[Sam_SAS]

Hello, and welcome!

I have been looking for a workaround to this, and the limitation is that you cannot use an aggregated measure in a filter.

Copying your data, I can calculate 1+2=3 as desired by using the AggregateTable() operator, but the output is an aggregated measure and you cannot filter on an aggregated measure.

AggregateTable(_Sum_, Table(_Sum_, Remove('Market'n),
'Market  1'n)) + AggregateTable(_Sum_, Table(_Sum_, Remove(
'Market'n), 'Market 2'n))

You CAN use an aggregated measure in a display rule, for what that is worth:

I suspect it should be possible to work around the issue in your data preparation stage, possibly as part of the join operation?

Alternatively, if you could redesign the report such that Market is no longer a column in the list table, then you could use a simple "LFT_MKT_FLAG + RT_MKT_FLAG" calculated measure and filter on that.

Someone else may be able to offer better ideas, but let us know if this helps,

Sam

[MikeBsocal]

Thank you Sam! I will try some of these options and report back.

Regarding this:

Alternatively, if you could redesign the report such that Market is no longer a column in the list table, then you could use a simple "LFT_MKT_FLAG + RT_MKT_FLAG" calculated measure and filter on that.

The final report will actually NOT have Market (I believe that was the second screenshot I sent). Using the final table without Market, I tried adding the two flags but the result was either 2 or 1 -- it appeared that VA was calculating at the data row level (which does contain Market), not the table row level (which does not contain Market). Is there a different way to create that simple "LFT_MKT_FLAG + RT_MKT_FLAG" other than, well, simply creating a calculated item that adds the two together? It would somehow have to ignore the Market dimension and/or do the calculation in the table itself...

Thanks!

Mike

[Sam_SAS]

You are correct that calculations and filters are evaluated at the row level. But for simple filters (not complex, expression-based filters) there is an option "Filter aggregated values" which should correctly filter the aggregated value of 3 when the Market column is excluded (and the Detail Data option is not enabled for the list table).

You can assign these flag columns to the "Hidden" data role and the filter will still work.

Let us know if that works. I think it should.

Thanks,

Sam

[MikeBsocal]

Thanks again Sam. As soon as time affords, I will try these ideas.

Regarding this:

for simple filters (not complex, expression-based filters) there is an option "Filter aggregated values" which should correctly filter the aggregated value of 3 when the Market column is excluded (and the Detail Data option is not enabled for the list table).

Are you saying Market has to be excluded from the source data? Or just from the table itself?

In my situation, I cannot alter the source data per se. Our reports have to be fully interactive for use by non-technical users. So I can't do one-off data prep. Everything we build (including data selection/filtering etc) has to be completely interactive and automated, based on a vast set of healthcare databases. Again -- we're new to Viya -- but our understanding is that we cannot make selections in Visual Analytics that filter down our data, then automatically move that filtered data into SAS Studio for more complex calculations, then automatically move the results of those complex calcs back to VA to feed our visualizations. Thus the non-technical user can make their selections and the rest happens behind the scenes, resulting in the charts etc they are looking for. Do you know if that's really the case?

[Sam_SAS]

---

@MikeBsocal wrote:

Are you saying Market has to be excluded from the source data? Or just from the table itself?

Only from the list table object,  not the source data table. Changing the source table would greatly simplify the reporting, so in some cases it is preferable if you have the permissions to do so.

---

@MikeBsocal wrote:

In my situation, I cannot alter the source data per se. Our reports have to be fully interactive for use by non-technical users. So I can't do one-off data prep. Everything we build (including data selection/filtering etc) has to be completely interactive and automated, based on a vast set of healthcare databases. Again -- we're new to Viya -- but our understanding is that we cannot make selections in Visual Analytics that filter down our data, then automatically move that filtered data into SAS Studio for more complex calculations, then automatically move the results of those complex calcs back to VA to feed our visualizations. Thus the non-technical user can make their selections and the rest happens behind the scenes, resulting in the charts etc they are looking for. Do you know if that's really the case?

---

Users can make selections that filter and otherwise affect the data in the current report query (through parameters that are used in calculated expressions, for example) but this does not alter the source data and cannot interact with SAS Studio in the way you describe. I think. 

If there are specific types of interactive features you want to implement, we can help you figure out how and whether it is possible.

Sam

[MikeBsocal]

Morning Sam

Okay to pick up on the addition of the two columns -- you're suggestion to simply add the two columns ('LFT MKT Flg'n + 'RT MKT Flg'n), without the Market dimension in the table, worked for the most part. I suspect I must not have checked the Filter Aggregated Value box when I first tried it.

That said I'm seeing a strange behavior. The two source columns LFT MKT Flg and RT MKT Flg are flags (with values of 0/1 and 0/2 respectively). Accordingly, the highest possible sum is 3. However when I try to filter for 3, I'm seeing a sum value of 4. Could this be due to some kind of duplicate record or something like that? It's strange because the two flags are 1 and 2 which should equal 3. See below. This only happens for two types of the drug Lotensin. That said, whatever the root cause, it indicates that the sum is not happening at the table level -- it must be including something we don't see -- correct?

I just investigated the source data and there are in fact a kind of duplicate of these two particular Lotensin NDC's (NDC is the unique identifier of a drug that indicates whether it's 5mg or 10mg or a tab or capsule, etc). 

Thanks

[Sam_SAS]

Thanks for the update, Mike. It sounds like there was just a data issue with duplicate values?

[MikeBsocal]

In addition to the question above, is there a way to use the summed column to filter the table with discrete values (eg. 0,1, and 3) as opposed to a continuous range of values (eg 3 thru 4, or 2 thru 4) ? Thanks!

[Sam_SAS]

I don't think so, no. If the flag columns were discrete / character instead of continuous numerics, then they would have separate rows in the list table and we couldn't filter on the aggregated values.

[MikeBsocal]

Ok thanks Sam. Just so I'm clear, I can't add those two columns and choose a particular set of numbers to filter on (like 1 and 3). They'd have to be "adjacent" numbers that I could filter in the slider (such as 0,1,2 or 1,2,3 or 2,3 or 3)?

Regarding the duplicates, they weren't duplicates per se -- they had another lesser-used dimension (in the source data, not in the List Table) that differed among the two. However I still have this question: the result of 4 indicates that the simple addition function isn't working at the table level, right? Since the table shows flags of 1 and 2 in that row, VA is grabbing that "duplicate" from somewhere and counting it, which then adds up to 4. Thoughts?

Again thanks for all your responses. They're definitely accelerating my VA skills.

[Sam_SAS · Accepted solution]

I'm not sure I understand about the "adjacent" numbers. Your flags could be 2 and 93, and your filter could check for 95. You can change the condition for the filter such that it selects a single value rather than a range. If that helps. However, as far as I know you cannot set an OR expression on the filter. All of this is a limitation of using aggregated filters. For filtering non-aggregated data, you can build complex expressions for your filter logic.

For handling your duplicates... the problem is that the two rows with 2 are being summed to get 4. So a way to work around this would be to use the MAX operator like so:

Max [_ByGroup_] ('Market  1'n) + Max [_ByGroup_] ('Market 2'n)

Let me know if that works. For me it seems to work. If you want to show the flag columns in your table, you could change the aggregation on the Data pane for those items to Max. This will make things make more sense visually in your list table, but it is still necessary to use the MAX operator when calculating your filter column. 

With MAX in your expression, you should only need to look for a value of 3. Duplicate values shouldn't affect the filter column.

Sam

[MikeBsocal]

Hi Sam

Okay that worked! I used this methodology:

LftFlg+RtFlg SUM = Max [_ByGroup_] ('LFT MKT Flg'n) + Max [_ByGroup_] ('RT MKT Flg'n)

I added LftFlg+RtFlg SUM as a filter on the object. If I filter for 1 & 2 then I get the items that are DIFFERENT between the two lists. The two NDC's in red are in the left list but not in the middle list. The right list shows the results of the comparison.

If I filter for 3, I get the items that are the SAME across the two lists. Thus the same two NDC's in red are now absent from the comparison list but all the other "NDC's in common" are present.

Now I have to expand this exercise. I need to include the Date selectors. In our example above, we compare two markets however the logic is agnostic of Date. We are required to compare the two markets across different delivery dates. And we're required to compare the same exact market across two delivery dates (markets can change over time). I'll work on that and report back. If you have any guidance, please pass along.

Thanks for your help as always

[Sam_SAS]

Great to hear it is working for you now!

With regard to dates, it is not quite clear what you will need to do and what the data will look like. I can probably help if you can offer more info and a more specific question.

Sam

[MikeBsocal]

Sam

Yes I was able to add in the Date criteria by simply adding the Delivery Date to the Market Flag calculated item:

IF ( ( 'MARKET'n Contains 'p_RX MKT RT1'p ) AND ( 'DLV DTE'n
Contains 'p_RX DTE RT1'p ) )
RETURN 2
ELSE 0

I'm using two Drop Down List Controls to create parameters for both the Market and the Delivery Date. In those controls, I select the Market and Delivery Date. Then the calculated item code, above, flags any data with the chosen Market and chosen Delivery Date as a 2 (else it is flagged 0).

I repeat this in a second object/List Table... I create the two drop down list controls, create the parameters, and create a second calculated item for a second flag (same code as above except I flag it 1 instead of 2).

In a third list table, using the same basic items I have in the first two list tables, I add the flags together. I have a filter on the object that is set to 3. Thus if the two flags are 1 and 2 (signifying that the data is in both the selected Market and the selected Delivery Date) they sum up to 3 and will show in the table.

I have a fourth table that does the same as above but I set the filter to 1 through 2; which shows me the drugs that are different among the two selected Markets/Delivery Dates.

Hope that all makes sense!

Thanks