topic How to get NVARCHAR2 in place of VARCHAR2 while creating Oracle table from sas dataset in SAS Programming

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

Abhay — Fri, 06 May 2016 11:45:28 GMT

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

How to get NVARCHAR2 in place of VARCHAR2 while creating Oracle table from sas dataset

Abhay — Fri, 06 May 2016 11:41:09 GMT

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.

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

RW9 — Fri, 06 May 2016 12:17:16 GMT

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.

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

LinusH — Sat, 07 May 2016 06:08:24 GMT

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