this code is taking lot of time

[aanan1417]

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 ;

[Kurt_Bremser · Accepted solution]

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.

[aanan1417]

608 data req_nw_rnw;
609 set si_yr;
610 if _n_ = 1
611 then do;
612 declare hash si (dataset:"si");
_
567
613 si.definekey("policy_no","si");
____________
557
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 567-185: Variable SI already defined.

ERROR 557-185: Variable si is not an object.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

614 si.definedone();
615 end;
616 if si.check() = 0;
617 run;
618

[Kurt_Bremser]

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,

[Tom]

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;