topic Re: need to change proc sql code into data step in SAS Programming

need to change proc sql code into data step

aanan1417 — Thu, 07 Apr 2022 08:18:04 GMT

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;

Re: need to change proc sql code into data step

Kurt_Bremser — Thu, 07 Apr 2022 08:29:21 GMT

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)

Re: need to change proc sql code into data step

Kurt_Bremser — Thu, 07 Apr 2022 08:34:37 GMT

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.

Re: need to change proc sql code into data step

aanan1417 — Thu, 07 Apr 2022 09:07:59 GMT

Could you pls explain this code

Re: need to change proc sql code into data step

Kurt_Bremser — Thu, 07 Apr 2022 10:15:05 GMT

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;

Re: need to change proc sql code into data step

aanan1417 — Thu, 07 Apr 2022 12:24:09 GMT

Thanks a ton