03-30-2015 10:16 AM
I was wondering if there is a way to output records that do not meet a selection criteria into a different dataset using SQL. How do I achieve the below without rewriting the opposite of the below query
PROC SQL NOPRINT;
CREATE TABLE EXTRACT.ACCOUNTS_FOR_M AS
IMPORT.BAC21(WHERE=(A.T_M_STYLE <> "ZZZ")) AS A
WHERE A.T_M_N IN (SELECT B.T_M_N FROM IMPORT.MMF20 AS B)
Thanks in advance....
03-30-2015 10:33 AM
I think this is best done with a data step.
then output output_with;
else output output_without;
Depending on your condition (how many items does SELECT B.T_M_N FROM IMPORT.MMF20 AS B render?), you may have to prepare your datasets for a merge first or use other tools like a format that gives you the condition, or a hash object.
03-31-2015 03:03 AM
The select returns about 4 million records. I am interested in finding records that exist in both datasets without dropping them with no visibility.
Any idea how will the merge function.
03-31-2015 03:16 AM
Then do the following:
data=import.bac21 (where=(T_M_STYLE <> "ZZZ"))
if b then output EXTRACT.ACCOUNTS_FOR_M;
else output missing_records;
03-31-2015 03:49 AM
Not sure what you mean by "without rewriting the opposite of the below query". Why not?. What is your actual requirement?
Is this a one time shot or something that will be executed regularly?
BTW having the where as a DS-option ads no value in this case. Moving the criteria to the WHERE clause will make the code more readable and clean.
03-31-2015 06:05 AM
Assuming that we're dealing with SAS tables here using a hash lookup could be quite efficient. See code below:
libname import (work);
libname extract (work);
data IMPORT.BAC21 IMPORT.MMF20;
do T_M_N=1 to 10;
if mod(t_m_n,2)=0 then output IMPORT.MMF20;
data EXTRACT.ACCOUNTS_FOR_M rest;
if _n_=1 then
if 0 then set IMPORT.MMF20(keep=T_M_N);
dcl hash h(dataset:'IMPORT.MMF20(keep=T_M_N)');
set IMPORT.BAC21(WHERE=(T_M_STYLE <> "ZZZ"));
if h.check()=0 then output EXTRACT.ACCOUNTS_FOR_M;
else output rest;