topic Re: How do I compare multiple values in one column to multiple values in another column in SAS Enterprise Guide

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

snelll — Wed, 02 May 2018 13:57:14 GMT

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

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

Reeza — Wed, 02 May 2018 14:53:02 GMT

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.

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

snelll — Wed, 02 May 2018 15:05:33 GMT

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.

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

ballardw — Wed, 02 May 2018 15:46:50 GMT

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-data-AKA-generate/ta-p/258712 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.

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

snelll — Tue, 08 May 2018 11:39:13 GMT

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