Date:         Mon, 13 Aug 2007 15:05:22 -0400
Reply-To:     Jack Clark <JClark@CHPDM.UMBC.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Clark <JClark@CHPDM.UMBC.EDU>
Subject:      Re: Creating Index on SQL Server Table with SAS and LIBNAME
              Statement
Comments: To: "Pardee, Roy" <pardee.r@GHC.ORG>
In-Reply-To:  A<8AD8F86B3312F24CB432CEDDA71889F203EF7BC5@ex06.GHCMASTER.GHC.ORG>
Content-Type: text/plain; charset="us-ascii"

Thank you to everyone for the suggestions.  Special thanks to Roy for
the sample code.  I got it working now.

I just had a little syntax problem with the pass-through facility code.
I was using 'Connect to SQLSRVR' instead of 'Connect to ODBC'.

As usual, SAS-L comes through.

Jack Clark
Research Analyst
Center for Health Program Development and Management
University of Maryland, Baltimore County


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Pardee, Roy
Sent: Monday, August 13, 2007 2:34 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Creating Index on SQL Server Table with SAS and LIBNAME
Statement

Bummer.  Nope--ODBC will let you do passthrough.  Here's some tested
code:

proc sql ;
   connect to odbc as dbserv (required = "DRIVER=SQL Server;
Trusted_Connection=Yes;DATABASE=my_database;SERVER=my_server") ;

   select * from connection to dbserv
   (create index field1_ix on gnu (field1) )
   ;

quit ;

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jack Clark
Sent: Monday, August 13, 2007 10:31 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Creating Index on SQL Server Table with SAS and LIBNAME
Statement

Roy,

Thank you, but those suggestions did not work.  They all return the
error message "ERROR: The HEADER/VARIABLE UPDATE function is not
supported by the ODBC engine."

I was reading the Pass-Through online doc, and tried a test using some
of the code in the example.  It did not work the way I had it coded
either, but that may be syntax problems.


One question I had, do you have to have SAS/ACCESS to SQL Server in
order to use the SQL Pass-Through Facility from SAS to SQL Server?

Thank you.


Jack Clark
Research Analyst
Center for Health Program Development and Management University of
Maryland, Baltimore County

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Pardee, Roy
Sent: Monday, August 13, 2007 11:29 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Creating Index on SQL Server Table with SAS and LIBNAME
Statement

Have you tried something like:

Proc sql ;
  alter table pharmdb.test add primary key (var1, var2) ;
  create index bubba on pharmdb.test (this_field, that_field) ;
  create index var1 on pharmdb.test ;
Quit ;

?

If that fails, you'll probably have to switch to pass-through.

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jack Clark
Sent: Monday, August 13, 2007 7:48 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Creating Index on SQL Server Table with SAS and LIBNAME
Statement

Hello,



I am working on replacing some of the PROC DBLOAD procedures in our
production code.  I am working on SAS Version 9.1 for Windows.  I have
successfully set up a LIBNAME statement pointing at the SQL Server
database, and successfully deleted and re-created tables in the
database.  My question is, what is the best way to create an INDEX on
the SQL Server table using this approach?



*** existing code ;

libname pharmdb odbc noprompt="dsn=mcocap_tmp;" bulkload=yes;





proc sql;

   drop table pharmdb.test

   ;

quit;





data pharmdb.test (dbtype=(recipno='char(11)' rx='numeric(5)'
pay='numeric(9,2)' ageend='numeric(5)'

                   remflag='numeric(1)' Coverage_group='char(15)'

                     county='char(16)' race='char(5)' sex='char(7)'
data_type='char(15)'));

   set xdb.rxmp_jan06_thru_jun06_demo (rename=(Cover=Coverage_group
type=Data_type));

run;




I tried the INDEX= data set option when creating PHARMDB.TEST and a
message in the log said that ODBC did not support that option.  I also
tried a separate PROC SQL statement after the SQL Server table was
created and got an error message saying "ERROR: The HEADER/VARIABLE
UPDATE function is not supported by the ODBC engine."



* not working ;

proc sql;

   create index recipno_ind on pharmdb.test(recipno)

   ;

quit;



Any suggestions are appreciated.  Thank you.







Jack Clark

Research Analyst

Center for Health Program Development and Management

University of Maryland, Baltimore County

410-455-6256