BookmarkSubscribeRSS Feed
Avenue
Calcite | Level 5

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

5 REPLIES 5
Kurt_Bremser
Super User

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
Calcite | Level 5

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
Super User

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
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 542 views
  • 6 likes
  • 4 in conversation