11-10-2017 11:02 AM - edited 11-10-2017 11:02 AM
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,
11-10-2017 11:09 AM
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.
11-10-2017 11:16 AM
I'm essentially searching for a count of any of the following text substrings within multiple variables, as shown below.
Substring searching for:
RS005 - CONSUMER.PRIMARY
RS006 - CONSUMER.PRIMARY
Variables to search within:
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.
11-10-2017 11:20 AM
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?
11-10-2017 11:35 AM
11-10-2017 11:44 AM
Then your best option is to use the TRANSPOSE task
Change your data structure so you have the data in a long format
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.....