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....
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.
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
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;
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.