Excluding rows when two variables have specific values on the same row with PROC SQL

[Sas_Act_114]

Hello,

I am pulling data from a database using PROC SQL and there is a WHERE condition I am unsure on how to input. The pull is one table with several columns (variables) and I want to exclude rows when two of the columns have a specific value each.

As an example, call the columns Alpha and Beta. If in one row, Alpha has the value C and in that same row, Beta has the value X, I do not want that row in the pull. But if Alpha has the value C and Beta has any other value but X, or if Beta has the value X and Alpha has any other value but C, I do want those rows. Any help on this is appreciated, thanks!

[ballardw]

Some concrete example of the sources and then the desired result.

In general SQL does not have a concept of "row order". There would have to be some other criteria provided to join the data on, or are you doing a full Cartesian Join of all rows from each table?

[Sas_Act_114]

I apologize if I wasn't clear enough in the original post. There is no joins, I am simply pulling the data from one table, which even that I do not believe to be relevant. For all intensive purposes, this just involves one table (or dataset if you will) with several variables/columns in the one table. As an example of the table (with just 3 columns):

ID  Alpha  Beta

1       C        X

2       P        X

3       C        T

4       P        T

5       C       X

When I pull this table, I want it to keep rows 2, 3 and 4, excluding 1 and 5 due to the Alpha having C and Beta having X. The pull is very simple, it is simply (code edited with general table name and source):

PROC SQL;
CONNECT TO ODBC;
CREATE TABLE TEST1 AS
SELECT *
FROM CONNECTION TO ODBC
(
SELECT *
FROM SOURCE.TABLE 

);DISCONNECT FROM ODBC;

I am looking for a where statement that will not include rows 1 and 5, as shown in the example above.

[Reeza]

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[ErikLund_Jensen · Accepted solution]

Hi @Sas_Act_114 

Try this in the inner select: where NOT (Alpha = "C" and Beta = "X")

[Sas_Act_114]

This was the command I needed, worked perfectly, thank you.