BookmarkSubscribeRSS Feed
Abhay
Calcite | Level 5

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

3 REPLIES 3
Abhay
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20
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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2622 views
  • 0 likes
  • 3 in conversation