Desktop productivity for business analysts and programmers

How do I compare multiple values in one column to multiple values in another column

Reply
New Contributor
Posts: 3

How do I compare multiple values in one column to multiple values in another column

I am fairly new to SAS and I am not a programmer. I have a report in which column A and Column B identify multiple organizations in which certain users have different responsibilities. In most cases, the organization name should be the same in both columns, however, we have discovered that this is not always the case. I would like to know if there is a way preferably in SAS VA or if not, in SAS EG that I run a query or report that will identify each row in which the value of Column A is not the same as the value in Column B.

Hoping you can help. Currently using SAS EG 7.1

Super User
Posts: 22,822

Re: How do I compare multiple values in one column to multiple values in another column

You can do it in Data Planner in SAS VA or EG.

 

Either way you're looking for ColA not equal to ColB which would be a filter in VA.

 

In EG, open a dataset, choose a filter and add the condition. However, if those are character fields, you may want to standardize them a bit first, because McDonalds is not the same as Mcdonalds.

 

 

New Contributor
Posts: 3

Re: How do I compare multiple values in one column to multiple values in another column

thanks for you very quick reply Reeza. We're going to give it a try and I'll let you know if it does the trick for us.

New Contributor
Posts: 3

Re: How do I compare multiple values in one column to multiple values in another column

Thanks everyone for you help. I was amazed at how quickly I got a response and will definitely reach out again when I need help. It turns out we were using the filter correctly but the data in two columns we were comparing were so slightly different (SSC|SPC  vs SSC-SPC) that we didn't notice this was why we were not getting the results we expected.

Your feedback made us look more closely as to why your advice confirmed our first approach but we were not getting the results. We used the filter in SAS EG as recommended by Reeza

Super User
Posts: 13,008

Re: How do I compare multiple values in one column to multiple values in another column

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Ask a Question
Discussion stats
  • 4 replies
  • 129 views
  • 0 likes
  • 3 in conversation