topic Re: how to create index through connect statement in SAS EG (odbc engine) in SAS Enterprise Guide

how to create index through connect statement in SAS EG (odbc engine)

Pato485 — Fri, 08 Sep 2023 07:25:36 GMT

how can i do this, but in sas eg (odbc engine) through implicit statements?

proc sql;
connect to sqlsvr as mydb
(datasrc="SASDATA" user="sas" password="xxx");
select * from connection to mydb
(CREATE INDEX ZP on dashboard_novacik (ZP) ;
CREATE INDEX Fin on dashboard_novacik (Fin) ;
);

QUIT;

This code works in SAS Studio (sqlsvr engine).

Thanks

Re: how to create index through connect statement in SAS EG (odbc engine)

Patrick — Fri, 08 Sep 2023 07:42:31 GMT

Both SAS EG and SAS Studio are clients that connect to a SAS Server where the code executes.

If you're connecting with both clients to the same environment with the same user then the same code will work using either client.

Not sure about SQL Server but with many databases a Create Index would fail if the index already exists - meaning you would need to either first drop the index or check for existence.

Something like:

Re: how to create index through connect statement in SAS EG (odbc engine)

Ksharp — Fri, 08 Sep 2023 11:59:14 GMT

"select * from connection to mydb" is for create a table.
If you want execute other SQL statement,your code should look like this:

proc sql;
connect to sqlsvr as mydb
(datasrc="SASDATA" user="sas" password="xxx");

execute(
CREATE INDEX ZP on dashboard_novacik (ZP) ;
CREATE INDEX Fin on dashboard_novacik (Fin) ;
) by mydb;

QUIT;

I am not very sure,check it more at support.sas.com
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0tpd3yaqvep53n1g8wahav3hgco.htm

Re: how to create index through connect statement in SAS EG (odbc engine)

Kurt_Bremser — Fri, 08 Sep 2023 13:43:58 GMT

Create a LIBNAME to the SQL Server schema, then run

proc sql;
create index zp on sqllib.dashboard_novacik (zp);
create index fin on sqllib.dashboard.novacik (fin);
quit;

But I would not be surprised if this does not work and you are forced to use explicit pass-through.