proc sql;
create table req_nw_rnw as
Select a.*
from si_yr a
where a.policy_no in (select b.Policy_no from si b where a.Policy_no=b.Policy_no
and A.SI = B.SI)
order by Policy_no ;
quit ;
Sub-selects are notoriously slow in SAS PROC SQL.
For a simple lookup on two key variables, use a hash in a data step:
data req_nw_rnw;
set si_yr;
if _n_ = 1
then do;
declare hash si (dataset:"si");
si.definekey("policy_no","si");
si.definedone();
end;
if si.check() = 0;
run;
proc sort data=req_nw_rnw;
by policy_no;
run;
The final sort is only necessary if si_yr is not already sorted.
Sub-selects are notoriously slow in SAS PROC SQL.
For a simple lookup on two key variables, use a hash in a data step:
data req_nw_rnw;
set si_yr;
if _n_ = 1
then do;
declare hash si (dataset:"si");
si.definekey("policy_no","si");
si.definedone();
end;
if si.check() = 0;
run;
proc sort data=req_nw_rnw;
by policy_no;
run;
The final sort is only necessary if si_yr is not already sorted.
My bad; you cannot name a hash object with the name of a variable which is already defined (through the SET statement).
Let's reduce the name of the hash to a single character:
data req_nw_rnw;
set si_yr;
if _n_ = 1
then do;
declare hash s (dataset:"si");
s.definekey("policy_no","si");
s.definedone();
end;
if s.check() = 0;
run;
This should work unless you also have a variable s in your dataset,
Just MERGE the datasets.
data req_nw_rnw;
merge si_yr(in=in1) si_b(in=in2);
by policy_no SI;
if in1 and in2;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.