Ask the experts / comment on column ...is

[alepage]

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'; ....

[Reeza]

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]

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]

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]

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]

Thanks you 

[Patrick]

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]

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 · Accepted solution]

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]

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]

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]

@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]

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]

Thank you for the information.