topic Re: Excluding rows when two variables have specific values on the same row with PROC SQL in SAS Programming

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

Sas_Act_114 — Thu, 13 Feb 2020 18:50:33 GMT

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!

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

ballardw — Thu, 13 Feb 2020 18:56:37 GMT

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?

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

Reeza — Thu, 13 Feb 2020 19:00:31 GMT

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

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

Sas_Act_114 — Thu, 13 Feb 2020 19:24:41 GMT

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.

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

ErikLund_Jensen — Thu, 13 Feb 2020 19:30:49 GMT

Hi @Sas_Act_114

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

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

Sas_Act_114 — Thu, 13 Feb 2020 19:49:04 GMT

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