topic this code is taking lot of time in New SAS User

this code is taking lot of time

aanan1417 — Thu, 12 May 2022 12:09:41 GMT

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 ;

Re: this code is taking lot of time

Kurt_Bremser — Thu, 12 May 2022 12:16:52 GMT

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.

Re: this code is taking lot of time

Tom — Thu, 12 May 2022 14:52:42 GMT

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;

Re: this code is taking lot of time

aanan1417 — Thu, 12 May 2022 15:09:33 GMT

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

Re: this code is taking lot of time

Kurt_Bremser — Fri, 13 May 2022 08:00:33 GMT

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,