BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aanan1417
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
Quartz | Level 8

Could you pls explain this code

Kurt_Bremser
Super User
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
Quartz | Level 8

Thanks a ton

Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1292 views
  • 3 likes
  • 2 in conversation