Execute in &tblspc not logged initially

[estersita]

Hi,

I am confused with a following statement from somebody code as I do not understand the syntax (never worked with SAS before):

proc sql;
connect to odbc as ...;

* Transient table is created and loaded

execute(
create table &TmpSchema..G
(ID CHAR(30)
,tag char (3)
,Mydate date
)
in "&twotblspc"
not logged initially
)by Mconnect;

execute (
insert into &TmpSchema..G
select
.
.
.
) byMconnect;

The only thing I got is a create table statement and load staff into that . Also I found out in the help on-line that Execute statement push generated statement (create table and select?) into the queue and returns

1.I wonder why execute is needed at all to create a table in this case or to insert staff into it?

2.Also I have no clue what in "&twotblspc" means in this context and what NOT LOGGED INITIALLY means

Could you please explain me?

Thank you very much

Estersita

 

[ChrisW75]

Hi Estersita, the "Not logged initially" doesn't look like a SAS statement to me, more like a comment, but there is no comment structure around it. Is that first execute statement copied exactly? And the line "* Transient table is created and loaded" should have a semi-colon at the end to end the comment, so according to the code up there, that whole first step doesn't get run at all as it is all comment.

The "&twotblspc" is a macro variable. Somewhere within the program you should see a line which starts "%let twotblspc=....." or "call symput('twotblspc',....)" which will define what its value is. Do a search on the string twotblspc and that should find it. If this block is within a macro, it could be a parameter passed in from the macro call. It's also possible that it is defined either in a script which runs the program, or in another SAS program which does a "%include" on this program.

And you are correct, the execute statement pushes the statement to the designated remote environment. What are you linking to? Oracle? Access? You need to use the execute in order to get the remote databases own system to create the table in the correct format. It saves SAS having to know exactly how to create that systems tables. We use this process to create/populate tables in Oracle as well.

 

[klaz2002]

The NOT LOGGED INITIALLY turns logging off on the SAS side. The Execute statement pushes the query to the outside database (oracle, access, SQL Server etc...). For a faster result you turn off the logging feature. (After extensive debugging).

The '&' sign is SAS's macro variable. These vars are set somewhere in your program through the use of the %let statement or a Call symput('xxx',); function.

Hope this has helped you.
Klaz

 

[ChrisW75]

Hi Klaz, wow, never heard of the NOT LOGGED INITIALLY option before, I'll have to look it up. Does it speed it up much?

 

[estersita]

Klaz,


Does it mean that I could eliminate the NOT LOGGED INITIALLY statement and et it just slower ?

execute(
create table &TmpSchema..G
(ID CHAR(30)
,tag char (3)
,Mydate date
)
in "&twotblspc"

)by Mconnect;

Thank you!

Estersita

 

[klaz2002]

While I cant be sure of the results of turning on the logging feature (by eliminating the LOGGING statement from your code) Why not try it and see if it impacts the speed/program in any way.

Chris-
This feature is an old SQL parameter and is not docuemnted in the recent docs. If you google it you will still find this option discussed on all the major SAS msg boards. have fun!
Klaz