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

Reply
Occasional Contributor
Posts: 13

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

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....

Super User
Posts: 6,933

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

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

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

Super User
Posts: 6,933

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,256

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

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.

Data never sleeps
Respected Advisor
Posts: 3,887

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

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;

Ask a Question
Discussion stats
  • 5 replies
  • 199 views
  • 6 likes
  • 4 in conversation