topic Re: run selected code in Proc SQL in SAS Enterprise Guide

run selected code in Proc SQL

yizhusas — Thu, 26 Oct 2017 21:53:37 GMT

In PC SAS, under one PROC SQL ... QUIT, we can select a particular set of codes and execute it, then do the same thing for another selected code, and etc.. But it looks this feature does not work in SAS EG, we have to run the entire Proc SQL, which makes the code testing process a little bit difficult, especially when it involves Teradata volatile table for instance.

Is there an option in SAS EG to turn this PC SAS feature on?

Re: run selected code in Proc SQL

Kurt_Bremser — Thu, 26 Oct 2017 22:05:27 GMT

No. Since EG sends "magic code" that is designed to clean up after any mistake, it also ends proc sql with quit;

To selectively run individual SQL steps, you have to wrap them into individual proc sql calls.

Re: run selected code in Proc SQL

yizhusas — Thu, 26 Oct 2017 22:16:13 GMT

that's bad. The problem is whenever we hit the quit, the volatile table will be gone, so I have no way to bring it into next proc sql. Hopefully SAS will add that feature into its future version of SAS EG.

Thanks

Re: run selected code in Proc SQL

Reeza — Thu, 26 Oct 2017 23:55:05 GMT

That actually sounds like something you may also be able to fix on the Teradata side, can you get your volatile tables to basically not be volatile and exist past a single session.

Re: run selected code in Proc SQL

yizhusas — Fri, 27 Oct 2017 00:11:36 GMT

figured it out. Actually I have to set my v-table as global by using the option "connection=global" in proc sql, then the v-table can be used in separated proc sql.

Re: run selected code in Proc SQL

kiranv_ — Fri, 27 Oct 2017 00:17:05 GMT

Are you using implicit pass through or explicit through for accessing volatile table? if it is explicit pass through run the queries in sql assistant and then all u need to do is wrap work execute and by teradata at the end.

Re: run selected code in Proc SQL

kiranv_ — Fri, 27 Oct 2017 00:20:39 GMT

one more thing you can use your volatile table in SAS in datastep or implicit pass through by using dbmstemp =yes. see the link below for more details

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p0he4t6yjfmkhpn16qrf0cdhllu6.htm&docsetVersion=9.4&locale=en

Re: run selected code in Proc SQL

yizhusas — Fri, 27 Oct 2017 00:29:59 GMT

absolutely it won't work without "dbmstemp=yes", I am using the explicit through. Have to define the libname first with the opinion "dbmstemp=yes" along with other teradata login statement.

Thanks.

Re: run selected code in Proc SQL

TomKari — Fri, 27 Oct 2017 01:51:24 GMT

Another option is SAS Studio. Apparently it has an "interactive mode", where an interactive procedure won't be terminated when submitted.

I can't test this, as I'm using Studio with SAS On Demand, which doesn't allow interactive mode.

Tom