BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

Hello,

 

The code below was provided by an Oracle user and I would like to used that code hopefully directly.

The creation of the table is relatively straight forward (NUM, VARCHAR() instead of NUMBER and VARCHAR2

 

However, I did not find information on comment on column ..is

Is it possible to translate that code into SAS code and if so, how do we do that?

Regards,

 

 

CREATE TABLE D9_CADAS_CITYN_SRC (
   ID_CITYN             NUMBER(15)            NOT NULL,
   PREN_CITYN           VARCHAR2(70)          NOT NULL,
   NOM_CITYN            VARCHAR2(70)          NOT NULL,
   COD_SEX_CITYN        VARCHAR2(1)           NOT NULL
       CHECK (COD_SEX_CITYN IN ('H','F','I'))
);

COMMENT ON TABLE D9_CADAS_CITYN_SRC IS
'Citizen Cadastre Table';

 

COMMENT ON COLUMN D9_CADAS_CITYN_SRC.COD_SEX_CITYN IS
'Code CITIZEN SEX
Here's the possible Value:
M - Man
W - Woman
U - Unknow'; ....

1 ACCEPTED SOLUTION

Accepted Solutions
Sven111
Pyrite | Level 9

I think something like this should work using Explicit Pass-Through SQL:

PROC SQL;
    CONNECT TO ORACLE ( USER=*** orapw=*** PATH=u.data)  AS OracDB;
    EXECUTE(CREATE TABLE D9_CADAS_CITYN_SRC (
               ID_CITYN             NUMBER(15)            NOT NULL
              ,PREN_CITYN           VARCHAR2(70)          NOT NULL
              ,NOM_CITYN            VARCHAR2(70)          NOT NULL
              ,COD_SEX_CITYN        VARCHAR2(1)           NOT NULL CHECK (COD_SEX_CITYN IN ('H','F','I')))
            ) BY OracDB;

    EXECUTE(COMMENT ON TABLE D9_CADAS_CITYN_SRC IS 'Citizen Cadastre Table') BY OracDB;
    EXECUTE(COMMENT ON COLUMN D9_CADAS_CITYN_SRC.COD_SEX_CITYN IS 'Code CITIZEN SEX Heres the possible Value: M - Man W - Woman U - Unknown') BY OracDB;

    DISCONNECT FROM OracDB;
QUIT;

View solution in original post

13 REPLIES 13
Reeza
Super User
Are you adding the 'Ask the experts' portion to every question? I thought that was part of the SAS posts. Either way, a better subject line helps everyone and the 'ask the experts' portion is unnecessary.
alepage
Barite | Level 11

I though it was better to add Ask the experts because I saw it on many post.

If you think it is unnecessary, I won't put it anymore.

 

Thanks 

Reeza
Super User

Think I've only seen it on your posts and the 'ask the experts' posts from SAS which are very different and more like a blog focused on a specific topic. It doesn't add any value to your subject line and if someone thinks it's a blog post which most are then they'll just skim over your question. 

How you chose to label them is ultimately your choice however, I'm a user, just like you are. 

 


@alepage wrote:

I though it was better to add Ask the experts because I saw it on many post.

If you think it is unnecessary, I won't put it anymore.

 

Thanks 


 

Sven111
Pyrite | Level 9

If you create the table in Oracle using Explicit Pass-Through SQL you should be able to use the exact same syntax as you would in Oracle.  I'm not sure if it's possible to do it using Implicit Pass-Through, but if you already have SQL DDL code it seems like a lot of extra work to try and translate it, if it's possible at all.

alepage
Barite | Level 11

Thanks you 

Patrick
Opal | Level 21

And to add to what @Sven111 wrote: 

I believe it's documented that the SAS/Access engines don't push comments to the database when creating the database create statement based on SAS flavor SQL or data step code. Also for this reason and to have full control always use explicit SQL pass-through for table creation with comments included.

 

And about "Ask the Expert..."  That's very much how Oracle forums are called but has nothing to do with the SAS forum here.

alepage
Barite | Level 11

Hello Patrick

 

I have made some progress...

 

As you have mentioned with the implicit approach the SAS / ACCESS engine does not push the comments.

 

This code is working but without the comment

 

proc sql;

connect to oracle ( user=*** orapw=*** path=u.data);

CREATE TABLE D9_CADAS_CITYN_SRC (

ID_CITYN NUM(15) NOT NULL,

PREN_CITYN VARCHAR(70) NOT NULL,

NOM_CITYN VARCHAR(70) NOT NULL,

COD_SEX_CITYN VARCHAR(1) NOT NULL

CHECK (COD_SEX_CITYN IN ('H','F','I')),

COD_ETAT_CIVIL VARCHAR(1) NOT NULL

CHECK (COD_ETAT_CIVIL IN ('C','M','D','S','V','J'))

 

/*COMMENT ON TABLE D9_CADAS_CITYN_SRC IS

'Citizen cadastre Table */

);

QUIT;

 

The PROC SQL Pass-Through Facility is working

 

proc sql;

connect to oracle ( user=*** orapw=*** path=u.data);

create table Test as

select * from connection to oracle

(select * from D9_CADAS_CITYN_SRC) ;

disconnect from oracle;

quit;

 

The PROC SQL Pass-Through Facility is working  but when I want to put more information about the variable, it does not take it */

 

Is there a way to improve the code below to make it working?

 

proc sql;

connect to oracle ( user=*** orapw=*** path=u.data);

create table Test as

select * from connection to oracle

(select (ID_CITYN NUMBER(15) NOT NULL)) from D9_CADAS_CITYN_SRC) ;

disconnect from oracle;

quit;

 

 

 

 

 

 

Sven111
Pyrite | Level 9

I think something like this should work using Explicit Pass-Through SQL:

PROC SQL;
    CONNECT TO ORACLE ( USER=*** orapw=*** PATH=u.data)  AS OracDB;
    EXECUTE(CREATE TABLE D9_CADAS_CITYN_SRC (
               ID_CITYN             NUMBER(15)            NOT NULL
              ,PREN_CITYN           VARCHAR2(70)          NOT NULL
              ,NOM_CITYN            VARCHAR2(70)          NOT NULL
              ,COD_SEX_CITYN        VARCHAR2(1)           NOT NULL CHECK (COD_SEX_CITYN IN ('H','F','I')))
            ) BY OracDB;

    EXECUTE(COMMENT ON TABLE D9_CADAS_CITYN_SRC IS 'Citizen Cadastre Table') BY OracDB;
    EXECUTE(COMMENT ON COLUMN D9_CADAS_CITYN_SRC.COD_SEX_CITYN IS 'Code CITIZEN SEX Heres the possible Value: M - Man W - Woman U - Unknown') BY OracDB;

    DISCONNECT FROM OracDB;
QUIT;
alepage
Barite | Level 11

Thanks seven111 for you help. it is very appreciated.

 

The only correction I have made is:

 

CONNECT TO ORACLE AS OracDB ( USER=*** orapw=*** PATH=u.data) instead of

CONNECT TO ORACLE ( USER=*** orapw=*** PATH=u.data)  AS OracDB;

 

 

 

 

 

Sven111
Pyrite | Level 9

Yep, you're right I was conflating the two different methods of connecting.  I usually setup all of my DB connections as LIBNAMES and then use the following:

 

LIBNAME OracSAS ORACLE USER=&UserNm PASS=&PassWd DEFER=YES PATH=&OracServiceName CONNECTION=GLOBAL SCHEMA=&schemaNm DBMAX_TEXT=32000;
PROC SQL; CONNECT USING OracSAS AS OracDB; /* Various SQL Statements */ DISCONNECT FROM OracDB; QUIT;

But if you're specifying the connection in the CONNECT you may need to do it your way.

 

Patrick
Opal | Level 21

@alepage

As @Sven111 demonstrated, all explicit pass-through code which doesn't return rows to SAS need to be in an EXECUTE block following syntax: EXECUTE ( <Oracle SQL> ) by Oracle;

Tom
Super User Tom
Super User

If you want to define a dataset then use a DATA step.

data  D9_CADAS_CITYN_SRC (label='Citizen Cadastre Table');
  length ID_CITYN 8 PREN_CITYN  $70 NOM_CITYN $70 COD_SEX_CITYN $1 ;
  label COD_SEX_CITYN="Code CITIZEN SEX : M - Man, W - Woman, U - Unknown";
  stop;
run;

You might want to also define a format 

proc format ;
  value $SEX 'M' = 'Man' 'W'='Woman' 'U'='Unknown';
run;

and attach it to your coded variable by adding a FORMAT statement to your data step.

format COD_SEX_CITYN $sex.;
alepage
Barite | Level 11

Thank you for the information.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 13 replies
  • 1981 views
  • 2 likes
  • 5 in conversation