topic Re: Output both records that meet and don't meet the selection criteria SQL in SAS Programming

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

Avenue — Mon, 30 Mar 2015 14:16:18 GMT

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

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

Kurt_Bremser — Mon, 30 Mar 2015 14:33:49 GMT

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.

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

Avenue — Tue, 31 Mar 2015 07:03:40 GMT

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

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

Kurt_Bremser — Tue, 31 Mar 2015 07:16:01 GMT

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;

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

LinusH — Tue, 31 Mar 2015 07:49:42 GMT

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.

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

Patrick — Tue, 31 Mar 2015 10:05:45 GMT

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;