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
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.
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.
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?
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.....
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.