Using _method option in proc sql

[juanvg1972]

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;

[ChrisBrooks · Accepted solution]

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.

[juanvg1972]

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

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

[SAS_inquisitive]

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