Data Types for Oracle

%let PTCONN= %str(user=scott pw=tiger path=oraclev10);
%let CONN= %str(user=scott pw=tiger  path=oraclev10);

options sastrace=",,," sastraceloc=saslog nostsuffix;

proc sql;
connect to oracle ( &PTCONN);

/*execute ( drop table EMP_ATTENDANCE) by oracle;*/

execute ( create table EMP_ATTENDANCE (  EMP_NAME VARCHAR2(10),
   arrival_timestamp TIMESTAMP, departure_timestamp TIMESTAMP ) ) by oracle;
execute ( insert into EMP_ATTENDANCE values
   ('John Doe', systimestamp, systimestamp+.2) ) by oracle;
execute ( insert into EMP_ATTENDANCE values
   ('Sue  Day', TIMESTAMP'1980-1-12 10:13:23.33',
       TIMESTAMP'1980-1-12 17:13:23.33' )) by oracle;
quit;

libname ora  oracle &CONN

proc contents data=ora.EMP_ATTENDANCE; run;

proc sql;
 /* reading TIMESTAMP datatype */
select * from ora.EMP_ATTENDANCE;
quit;

 /* appending to TIMESTAMP datatype */
data work.new;
 EMP_NAME='New  Bee1';
 ARRIVAL_TIMESTAMP='30sep1998:14:00:35.00'dt;
 DEPARTURE_TIMESTAMP='30sep1998:17:00:14.44'dt;  output;
 EMP_NAME='New  Bee2';
 ARRIVAL_TIMESTAMP='30sep1998:11:00:25.11'dt;
 DEPARTURE_TIMESTAMP='30sep1998:14:00:35.27'dt;  output;
 EMP_NAME='New  Bee3';
 ARRIVAL_TIMESTAMP='30sep1998:08:00:35.33'dt;
 DEPARTURE_TIMESTAMP='30sep1998:17:00:35.10'dt; output;
 format ARRIVAL_TIMESTAMP datetime23.2;
 format DEPARTURE_TIMESTAMP datetime23.2;
run;

title2 'After append';
proc append data=work.new base=ora.EMP_ATTENDANCE ; run;
proc print data=ora.EMP_ATTENDANCE ; run;

 /* updating TIMESTAMP datatype */
proc sql;
update ora.EMP_ATTENDANCE set ARRIVAL_TIMESTAMP=. 
   where EMP_NAME like '%Bee2%' ;

select * from ora.EMP_ATTENDANCE ;

delete from ora.EMP_ATTENDANCE where EMP_NAME like '%Bee2%' ;

select * from ora.EMP_ATTENDANCE ;

 /* OUTPUT: Creating a brand new table using Data Step*/
data work.sasdsfsec; c_ts='30sep1998:14:00:35.16'dt; k=1; output;
                     c_ts='.'dt; k=2; output;
                     format c_ts datetime23.2; run;

/* picks default TIMESTAMP type */
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
data ora.tab_tsfsec; set work.sasdsfsec; run;
options sastrace=",,," sastraceloc=saslog nostsuffix;
proc datasets library=ora;
  delete tab_tsfsec;run;

/* Override the default datatype */
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
data ora.tab_tsfsec (dbtype=(c_ts='timestamp(3)'));
 c_ts='30sep1998:14:00:35'dt;
 format c_ts datetime23.; run;
options sastrace=",,," sastraceloc=saslog nostsuffix;
proc datasets library=ora;
  delete tab_tsfsec;run;

proc print data=ora.tab_tsfsec; run;

/* OUTPUT: Brand new table creation with bulkload=yes */
title2 'Test OUTPUT with bulkloader';
proc datasets library=ora;
  delete tab_tsfsec;run;

/* picks default TIMESTAMP type */
data ora.tab_tsfsec (bulkload=yes); set work.sasdsfsec; run;
proc print data=ora.tab_tsfsec; run;

proc sql;
connect to oracle ( &PTCONN);
execute ( drop table PRODUCT_INFO) by oracle;

execute (
 create table PRODUCT_INFO (  PRODUCT VARCHAR2(20), 
    LIST_PRICE number(8,2),
    WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH )
)by oracle;
execute (
  insert into PRODUCT_INFO values ('Dish Washer', 4000, '02-00')
)by Oracle;
execute (
  insert into PRODUCT_INFO values ('TV', 6000, '03-06')
)by Oracle;
quit;

proc contents data=ora.PRODUCT_INFO; run;

 /* Shows WARRANTY_PERIOD as number of months */
proc print data=ora.PRODUCT_INFO; run;

 /* Shows WARRANTY_PERIOD in a format just like in Oracle*/
proc print 
   data=ora.PRODUCT_INFO(dbsastype=(WARRANTY_PERIOD='CHAR(6)')); run;

 /* Add a new product */
data new_prods;
   PRODUCT='Dryer';  LIST_PRICE=2000;WARRANTY_PERIOD=12;
run;

proc sql;
insert into ora.PRODUCT_INFO select * from new_prods;
select * from ora.PRODUCT_INFO;
select * from ora.PRODUCT_INFO  where WARRANTY_PERIOD > 24;
quit;

proc sql;
connect to oracle ( &PTCONN);
execute ( drop table PERF_TESTS) by oracle;

execute (
 create table PERF_TESTS (  TEST_NUMBER number(4) primary key,
    TIME_TAKEN INTERVAL DAY TO SECOND )
)by oracle;

execute (
  insert into PERF_TESTS values (1,  '0 00:01:05.000200000')
)by Oracle;

execute (
  insert into PERF_TESTS values (2,  '0 00:01:03.400000000')
)by Oracle;

quit;

proc contents data=ora.PERF_TESTS; run;

 /* Shows TIME_TAKEN as number of seconds */
proc print data=ora.PERF_TESTS; run;

 /* Shows TIME_TAKEN in a format just like in Oracle*/
proc print 
  data=ora.PERF_TESTS(dbsastype=(TIME_TAKEN='CHAR(25)')); run;

 /* Add a new test*/
data new_tests;
  TEST_NUMBER=3; TIME_TAKEN=50;
run;

proc sql;
insert into ora.PERF_TESTS select * from new_tests;
select * from ora.PERF_TESTS;

select * from ora.PERF_TESTS  where TIME_TAKEN < 60;
quit;