Date:         Mon, 5 Apr 2010 13:09:54 -0400
Reply-To:     JKR <jayakumarreddy@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         JKR <jayakumarreddy@GMAIL.COM>
Subject:      Creating a DBMS Table using EXECUTE statement in Pass through

Hello All
i am trying to create a SQL server DBMS table within SAS and fill it with
obs from SAS Dataset. I have set up the ODBC connections correctly and this
is the script i run.

PROC SQL;
 CONNECT TO ODBC (DATABASE=BRSDATA);
 CREATE TABLE sql.UPDATE_OPSF_POS_2010 as
 SELECT * FROM sqlpass.FINAL_HOSPITAL_DATA;
 DISCONNECT FROM ODBC;
quit;

but i could not change the datatypes in SQL server. It says i do not have
the privileges. I wanted to Know if the GRANT option in EXECUTE statement
might help me change the datatypes in SQL Server if i added that in the
script. I also tried the Create table in Execute statement like the
following but it shows an error

PROC SQL;
 CONNECT TO ODBC (DATABASE=BRSDATA);
  SELECT * FROM sqlpass.FINAL_HOSPITAL_DATA;
  EXECUTE(create table sql.UPDATE2_OPSF_POS_2010 (HO_NUM CHAR(6)not null,
                                                         HO_EFF_BEGIN
smalldatetime not null,
                                                                HO_EFF_END
smalldatetime not null,
                                                                HO_IMP_BEGIN
smalldatetime not null,
                                                                HO_IMP_END
smalldatetime not null,

HO_RURAL_STATUS CHAR(1) null,
                                                                HO_STATE
CHAR(2) null,
                                                                HO_ZIP
CHAR(5) null,
                                                                HO_PLUS_FOUR
CHAR(4) null,
                                                                HO_TELEPHONE
CHAR(10) null,
                                                                HO_COUNTY
CHAR(3) null,
                                                                HO_CAH
CHAR(1) null,
                                                                HO_NAME
VARCHAR(62) null,
                                                                HO_ADDRESS
VARCHAR(50) null,
                                                                HO_CITY
VARCHAR(30) null,

HO_BRS_LOC_ID_1 int null,

HO_BRS_LOC_ID_2 int null,
                                                                HO_NPI
CHAR(10)null,
                                                                GEO_CBSA
CHAR(5)null,
                                                                WI_CBSA
CHAR(5)null

PROVIDER_TYPE CHAR(2) null)) BY ODBC;

 DISCONNECT FROM ODBC;
 quit;

Thanks in advance

It says the following in log

4961  PROC SQL;
4962      CONNECT TO ODBC (DATABASE=BRSDATA);
4963  /**  CREATE TABLE sql.UPDATE_OPSF_POS_2010 as**/
4964          SELECT * FROM sqlpass.FINAL_HOSPITAL_DATA;
4965  **      EXECUTE(grant select on sql.UPDATE_OPSF_POS_2010) by ODBC;
4966          EXECUTE(create table sql.UPDATE2_OPSF_POS_2010 (HO_NUM
CHAR(6)not null,
4967                                                        HO_EFF_BEGIN
smalldatetime not null,
4968
HO_EFF_END smalldatetime not null,
4969
HO_IMP_BEGIN smalldatetime not null,
4970
HO_IMP_END smalldatetime not null,
4971
HO_RURAL_STATUS CHAR(1) null,
4972
HO_STATE CHAR(2) null,
4973                                                                  HO_ZIP
CHAR(5) null,
4974
HO_PLUS_FOUR CHAR(4) null,
4975
HO_TELEPHONE CHAR(10) null,
4976
HO_COUNTY CHAR(3) null,
4977                                                                  HO_CAH
CHAR(1) null,
4978
HO_NAME VARCHAR(62) null,
4979
HO_ADDRESS VARCHAR(50) null,
4980
HO_CITY VARCHAR(30) null,
4981
HO_BRS_LOC_ID_1 int null,
4982
HO_BRS_LOC_ID_2 int null,
4983                                                                  HO_NPI
CHAR(10)null,
4984
GEO_CBSA CHAR(5)null,
4985
WI_CBSA CHAR(5)null
4986
'PROVIDER_TYPE' CHAR(2) null)) BY ODBC;
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near 'PROVIDER_TYPE'.
4987
4988      DISCONNECT FROM ODBC;

What am i doing wrong?



Thanks For your help

JKR