BookmarkSubscribeRSS Feed
CWilson4815
Fluorite | Level 6

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 

5 REPLIES 5
Reeza
Super User

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.

CWilson4815
Fluorite | Level 6

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.

Reeza
Super User

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?

CWilson4815
Fluorite | Level 6
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.
Reeza
Super User

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

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!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 893 views
  • 0 likes
  • 2 in conversation