Desktop productivity for business analysts and programmers

Ask the experts / comment on column ...is

Accepted Solution Solved
Reply
Regular Contributor
Posts: 167
Accepted Solution

Ask the experts / comment on column ...is

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


Accepted Solutions
Solution
3 weeks ago
Frequent Contributor
Posts: 102

Re: Ask the experts / comment on column ...is

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


All Replies
Super User
Posts: 23,958

Re: Ask the experts / comment on column ...is

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.
Regular Contributor
Posts: 167

Re: Ask the experts / comment on column ...is

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 

Super User
Posts: 23,958

Re: Ask the experts / comment on column ...is

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 


 

Frequent Contributor
Posts: 102

Re: Ask the experts / comment on column ...is

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.

Regular Contributor
Posts: 167

Re: Ask the experts / comment on column ...is

Thanks you 

Respected Advisor
Posts: 4,779

Re: Ask the experts / comment on column ...is

[ Edited ]

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.

Regular Contributor
Posts: 167

Re: Ask the experts / comment on column ...is

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;

 

 

 

 

 

 

Solution
3 weeks ago
Frequent Contributor
Posts: 102

Re: Ask the experts / comment on column ...is

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;
Regular Contributor
Posts: 167

Re: Ask the experts / comment on column ...is

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;

 

 

 

 

 

Frequent Contributor
Posts: 102

Re: Ask the experts / comment on column ...is

[ Edited ]

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.

 

Respected Advisor
Posts: 4,779

Re: Ask the experts / comment on column ...is

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

Super User
Super User
Posts: 8,264

Re: Ask the experts / comment on column ...is

[ Edited ]

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.;
Regular Contributor
Posts: 167

Re: Ask the experts / comment on column ...is

Thank you for the information.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 268 views
  • 2 likes
  • 5 in conversation