Hi,   I 'm creating a new table in a proc sql using a table that has considerable lengths.   NAME TYPE LENGTH Variable1 2 5000 Variable2 2 255 Variable3 2 15000 Variable4 2 5000 Variable5 2 5000 Variable6 2 15000 Variable7 2 5000 Variable8 2 25 Variable9 1 8 Variable10 2 255 Variable11 1 8 Variable12 2 5000 Variable13 2 5000 Variable14 2 15000 Variable15 1 8   This proc sql gives the following output :   ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000000278'n because the length of the index value (15026 bytes) is too large. The index page size (currently 32767) must be large enough to store three index values plus a small overhead structure. Reduce the length of the index value or use the IBUFSIZE= option to increase the index page size (up to a maximum of 32,767 bytes).   I alread found out that reducing these lengths solves the problem, however I'm stuck with a couple of questions as I don't understand exactly why the ERROR is happening : 1. What does 'the length of the index value'  mean in the error message ? 2. The maximum IBUFSIZE option 32,767 did not work for me. Still I got the error message.... Is there another solution apart from reducing the lengths ? 3. The input data set for the proc sql is not big at all. Only 108 records. It seems that the length of the fields influences a memory buffer. Is that right ? How to prevent this by default ? 4. What is this tmpindex and how does this relate tot the index page size ? I'm confused by the word index because I normally consider this a method to make queries faster on fields that are indexed   Thanks for clarifying ! Regards B     ... View more

Hi I have 4 questions. regarding this : Question 1 : I'm creating temporary tables in an SQL server database. This is the way I do it : LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN> PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500; PROC SQL ; CREATE TABLE sqldb.'##TEST'n(INSERTBUFF=1000) AS select distinct <VARIABLE> from SQLSERVERLIB.SQLSVRTABLE; quit; This works ! However when I check SQL Server Management Studio, I do not see #TEST in the tempdb ( I check for Databases\System Databases\tempdb\Temporary Tables folder ) What do I need to do differently to make this temp table appear in this folder ? Question 2 I am getting the following error message : ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'select'. when I do this : proc sql; connect to odbc as test (dsn=<DB> authdomain=<AD> connection=shared); execute( create table ##T1 as select * from ##TEST ) by test; quit; Why can't I do this ? Question 3 LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN> PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500; PROC SQL; connect to odbc as test (dsn=<DB> authdomain=<AD> connection=shared); EXECUTE ( create table ##T11 (bank_code VARCHAR(30) ) ) BY A; quit; data T11; set sqldb.'##T11'n; run; This EXECUTE statement does work ! However why can't I get it from library SQLDB just like the sql in question 1 ? Question 4 So far I've been using this lib statement to connect to the tempdb on MS SQL server : LIBNAME sqldb ODBC DATASRC=<DB> SCHEMA=dbo authdomain=<AUTHDOMAIN> PRESERVE_TAB_NAMES=YES CONNECTION=SHARED INSERTBUFF=1000 READBUFF=2500; As you can see I'm referring to a specific database whereas the temp tables should be available across ALL databases. Is there a different statement which does this ? In Teradata I always used : libname volalib teradata authdomain=<authdomain> mode=teradata server=<"server"> connection=global dbmstemp=yes;   Is there a similar MS SQL Statement ?   Thanks for you help !!   Regards BB   ... View more

Hi, I'm facing a problem with explicit pass throughs to SQL server database. The following code works fine : options sastrace=',,,d' sastraceloc=saslog nostsuffix; %let dsn = <mydatabase>; %let authdomain = <myauthdomain>; libname tempdb odbc dsn=&dsn. authdomain=&authdomain. connection=shared; data test; date = '01JAN2019'd ; datetime = '01JAN2019:00:00'dt ; run; data tempdb.'#test'n; set test; run; data test; set tempdb.'#test'n; run;   However when I do the following : proc sql; connect to odbc as test (dsn=&dsn. authdomain=&authdomain. connection=shared); execute ( create table #datumtijd ( datum DATE, datumtijd DATETIME, ) ) by test; quit; data test; set tempdb.'#datumtijd'n; run;   I get the message : ERROR: File TEMPDB.'#datumtijd'n.DATA does not exist. Why is that ?   I would like to insert the data from '#test'n into '#datumtijd'n. Like this : proc sql; connect to odbc as test (dsn=&dsn. authdomain=&authdomain. connection=shared); execute ( INSERT INTO #datumtijd SELECT * FROM #test) by test ; disconnect from odbc; quit;   Thanks for your help !! Regards BB   ... View more