need to change proc sql code into data step

[aanan1417]

Hi sir,

this code is taking almost 3 hour to complete when i  ran it .

that why i want to change this code in data step so that it will take less time could you pls help me with this

proc sql;
create table gxhi2 as select * from gxhi1
where exists (select 1 from PROD_POL_MAPPING where PRODTYP = PROD_POL_MAPPING.Product_Type) ;*/
quit;

[Kurt_Bremser]

Use a hash:

data gxhi2;
set gxhi1;
if _n_ = 1
then do;
  declare hash p (dataset:"prod_pol_mapping (keep=product_type rename=(product_type=prodtype))");
  p.definekey("prodtype");
  p.definedata("prodtype");
  p.definedone();
end;
if p.check() = 0;
run;

(assuming that prodtype is a variable in gxhi1)

[aanan1417]

Could you pls explain this code

[Kurt_Bremser · Accepted solution]

data gxhi2; /* create this new dataset */
set gxhi1; /* read all variables from this dataset */
if _n_ = 1 /* in the first iteration of the data step */
then do;
  /* define a hash object */
  declare hash p (dataset:"prod_pol_mapping (keep=product_type rename=(product_type=prodtype))");
  /* define the object from a dataset where we keep one variable and rename it, so the names fit */
  p.definekey("prodtype"); /* define the single variable as key */
  p.definedata("prodtype"); /* and also as data, this reduces the memory footprint of the object */
  p.definedone(); /* definition complete */
end;
if p.check() = 0; /* the check method returns a zero when an entry is found */
run;

[aanan1417]

Thanks a ton

[Kurt_Bremser]

PS

select 1 from PROD_POL_MAPPING 

is very dangerous; if the order of variables in PROD_POL_MAPPING changes for whatever reason, this will not work as desired (but might not even issue a NOTE). Always address variables by name.