topic Re: Using _method option in proc sql in SAS Programming

Using _method option in proc sql

juanvg1972 — Thu, 14 Sep 2017 21:13:12 GMT

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;

Re: Using _method option in proc sql

ChrisBrooks — Thu, 14 Sep 2017 21:40:58 GMT

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.

Re: Using _method option in proc sql

SAS_inquisitive — Thu, 14 Sep 2017 22:26:03 GMT

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

Re: Using _method option in proc sql

juanvg1972 — Fri, 15 Sep 2017 11:13:19 GMT

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

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