How to create a table with NVARCHAR2 data type in oracle from SAS

[Abhay]

Hi,

I need to create a table with NVARCHAR2 property in Oracle for all char columns from sas. Please see an example below. Currently it's created as VARCHAR2.

proc sql;

create table STUDYPDR.tmp as

select schema_s as schema_s

from study_s;

quit;

Any help will be appreciated

[Abhay]

Hi Team,

I am creating a table in oracle from SAS e.g.

proc sql;

create table STUDYPDR.tmp as

select schema_s as schema_s

from study_s;

quit;

schema_s is char datatype in sas, when it gets created in oracle the datatype becomes varchar2. I want to create a table with NVARCHAR2, Ny way in PROC SQL or using LIBNAME.

Basically I have a big dataset, Is there any way to create NVARCHAR2 for all columns with char datatype while creating in oracle. Ny help will be appreciated.

[RW9]

Hi,

No need to post two questions on the same topic.  I would strongly advise that you use the database to create tables and views etc.  SAS is not the same as the Database.  You can still alter insert data into the table from SAS, but even then I would advise using the tools associated with the database for such a purpose.  

If you have to do it then you would need to pass-through the code to the database SQL parser, as SAS doesn't have nvarchar2 as that is a very Oracle centric thing - its not ANSI SQL.

So something like:

proc sql;
  connect to oracle (path...);
  execute (create table 'XYZ' (abc nvarchar2(20)))  by oracle;
  disconnect from oracle;
quit;

However, again, I recommend using the database to process this Not SAS.

[LinusH]

Actually there is a way from SAS to specify the data type for a target column. Use the DBTYPE= data set option.