Output both records that meet and don't meet the selection criteria SQL

[Avenue]

Hi Guys,

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

       SELECT A.*

    FROM

    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)

;

QUIT;

Thanks in advance....

[Kurt_Bremser]

I think this is best done with a data step.

data

  output_with

  output_without

;

set have;

if condition

then output output_with;

else output output_without;

run;

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.

[Avenue]

Hi,

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.

Thanks

[Kurt_Bremser]

Then do the following:

proc sort

  data=import.bac21 (where=(T_M_STYLE <> "ZZZ"))

  out=int_a

;

by T_M_N;

run;

proc sort

  data=import.mmf20 (keep=T_M_N)

  out=int_b

;

by T_M_N;

run;

data

  EXTRACT.ACCOUNTS_FOR_M

  missing_records

;

merge

  int_a (in=a)

  int_b (in=b)

;

by T_M_N;

if a;

if b then output EXTRACT.ACCOUNTS_FOR_M;

else output missing_records;

run;

[LinusH]

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.

[Patrick]

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;

  T_M_STYLE='abc';

  do T_M_N=1 to 10;

    output IMPORT.BAC21;

    if mod(t_m_n,2)=0 then output IMPORT.MMF20;

  end;

  stop;

run;

data EXTRACT.ACCOUNTS_FOR_M rest;

  if _n_=1 then

    do;

      if 0 then set IMPORT.MMF20(keep=T_M_N);

      dcl hash h(dataset:'IMPORT.MMF20(keep=T_M_N)');

      _rc=h.defineKey('T_M_N');

      _rc=h.defineDone();

      drop _rc;

    end;

  set IMPORT.BAC21(WHERE=(T_M_STYLE <> "ZZZ"));

  if h.check()=0 then output EXTRACT.ACCOUNTS_FOR_M;

  else output rest;

run;