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!
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?
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.
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.