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

Reply
Occasional Contributor
Posts: 6

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

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

Occasional Contributor
Posts: 6

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

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.

Super User
Super User
Posts: 7,720

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

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.

Super User
Posts: 5,388

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

Actually there is a way from SAS to specify the data type for a target column. Use the DBTYPE= data set option.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 478 views
  • 0 likes
  • 3 in conversation