DATA Step, Macro, Functions and more

Using _method option in proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

Using _method option in proc sql

Hi,

 

I use the _method in proc sql in order to know the execution plan, but I have a question, ¿is there anyway to change the execution plan in SAS?. I know that creating an index can change the execution plan, but I dob't know other options...

Any help will be greatly appreciated

 

proc sql _method;
create table prueba3 as
(select cod_articulo, sum(hc_venta) as ag_venta 
from prueba1 
group by cod_articulo);
quit;

Accepted Solutions
Solution
‎09-15-2017 04:47 AM
Super Contributor
Posts: 441

Re: Using _method option in proc sql

Posted in reply to juanvg1972

You can try the "Magic" option detailed in this paper https://www.mwsug.org/proceedings/2012/S1/MWSUG-2012-S109.pdf but I rather suspect that the SQL Optimiser will normally pick the best plan anyway. By the way if you want to learn more about Proc SQL Kirk Lafler (the author of the linked paper) is the acknowledged guru on the subject. A google search should uncover lots of papers he's written.

View solution in original post


All Replies
Solution
‎09-15-2017 04:47 AM
Super Contributor
Posts: 441

Re: Using _method option in proc sql

Posted in reply to juanvg1972

You can try the "Magic" option detailed in this paper https://www.mwsug.org/proceedings/2012/S1/MWSUG-2012-S109.pdf but I rather suspect that the SQL Optimiser will normally pick the best plan anyway. By the way if you want to learn more about Proc SQL Kirk Lafler (the author of the linked paper) is the acknowledged guru on the subject. A google search should uncover lots of papers he's written.

Frequent Contributor
Posts: 122

Re: Using _method option in proc sql

Posted in reply to ChrisBrooks

Thanks, MAGIC= doesn't work...as you said

 

Other question...¿is possible to use hints (similar to Oracle) in SAS?

Super Contributor
Posts: 271

Re: Using _method option in proc sql

Posted in reply to juanvg1972

 You can also specify _tree option.  I think  you'll get better answers in SQL communities in Stackoverflow.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 179 views
  • 1 like
  • 3 in conversation