topic Re: Replicate the Excel OR function in query builder computed column in SAS Enterprise Guide

Replicate the Excel OR function in query builder computed column

CWilson4815 — Fri, 10 Nov 2017 16:02:47 GMT

Hi guys,

I am searching for a few substrings within some variables in my data table and would like to avoid setting up multiple computed columns to count each item before summing them all up at the end.

For example, the following code works fine for returning the count of instances that 'RS006 - CONSUMER.PRIMARY' appears but I would like to expand this to search for 'RS005 - CONSUMER.PRIMARY', 'RS005' and 'RS006' all in one line of code for my query builder.

COUNT(CATX(',',DECISIONOTHERCODE1,DECISIONOTHERCODE2,DECISIONOTHERCODE3),'RS006 - CONSUMER.PRIMARY')

I would use the OR function in Excel to do this but I'm not sure what the equivalent is in SAS.

As I say I could set up multiple computed columns for each substring but I would like it to be a bit more efficient than that.

Thanks in advance,

Chris

Re: Replicate the Excel OR function in query builder computed column

Reeza — Fri, 10 Nov 2017 16:09:04 GMT

I think you'll get better answers if you explain what you're trying to do and include sample data.

There are multiples to solve a problem and I suspect this is not the most efficient by far...

And yes, you can replicate the OR from Excel in SAS quite easily but I suspect in this case it's not a good solution.

A summary task with a filter may be a better option for example.

Re: Replicate the Excel OR function in query builder computed column

CWilson4815 — Fri, 10 Nov 2017 16:16:43 GMT

Hi Reeza,

I'm essentially searching for a count of any of the following text substrings within multiple variables, as shown below.

Substring searching for:

RS005

RS006

RS005 - CONSUMER.PRIMARY

RS006 - CONSUMER.PRIMARY

Variables to search within:

DECISIONOTHERCODE1

DECISIONOTHERCODE2

DECISIONOTHERCODE3

...

DECISIONOTHERCODE10

So if any of the substrings are found, it will return the count of all of them.

This is normally 0 or 1 but could be more than 1.

Re: Replicate the Excel OR function in query builder computed column

Reeza — Fri, 10 Nov 2017 16:20:33 GMT

Are you looking for a count of records or a count of instances?

Ie if it's found in one variable DecisionOtherCode1, does it matter if it's in DecisionOtherCode99?

The easiest way to do this is to transpose your data to a long format and then use a single query. In general, having your data in a long format will make it easier to work with. Otherwise, an array within a data step is the next option.

SQL can do it, but it's the most verbose method.

Are you comfortable programming in SAS or are you using EG GUI?

Re: Replicate the Excel OR function in query builder computed column

CWilson4815 — Fri, 10 Nov 2017 16:35:45 GMT

The way the data is presented, each substring will only ever be in one of the variables but there could be an occasion where both RS005 and RS006 are found for example. Which would mean that the value returned would be 2.

I hope this is clear enough!

I'm not comfortable with programming and would prefer to keep it within EG query builders for now if possible.

Re: Replicate the Excel OR function in query builder computed column

Reeza — Fri, 10 Nov 2017 16:44:54 GMT

Then your best option is to use the TRANSPOSE task

Change your data structure so you have the data in a long format

DiagnosisOtherCode1 Value

DiagnosisOtherCode2 Value

DiagnosisOtherCode3 Value

DiagnosisOtherCode4 Value

DiagnosisOtherCode5 Value

Then you can use the Query Builder and use the filter to filter out the results you want using contains.

Where value contains ('RS006') or value contains ('RS0005') etc.....