SAS Interact With Oracle

Interact With Oracle:

/* Create table and insert data */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (create table emp6(eid number, age number, position char(15), salary number)) by oracle;

Execute (insert into emp6 values(234,45,’tester’,4500))

By oracle;

Execute (insert into emp6 values (235,25,’progrmmer’, 4500))

By oracle;

Disconnect from oracle;

Quit;

/* To update the existed table */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (update emp6 set salary = salary + 2000)

By oracle;

Disconnect from oracle;

Quit;

/* To create new variable in emp6 table (oracle) & loading the data */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (after emp6 add bonus number)

By oracle;

Execute (update emp6 set bonus = salary * 0.5)

By oracle;

Disconnect from oracle;

Quit;

/* To drop required column from the oracle tables */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (after table emp6 drop column age)

By oracle;

Disconnect from oracle;

Quit;

Note:

If we run pass through facility we will get any execution message (notes) in log window. That’s why these statements are executed in other environment.

/* To delete the observations */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (delete from emp6 where position = ‘tester’)

By oracle;

Disconnect from oracle;

Quit;

Aspired to become SAS Developer? Explore the post to discover the know-hows on SAS Training In Pune.

/* To drop emp6 table */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Execute (drop table emp6)

By oracle;

Disconnect from oracle;

Quit;

/* To create views or tables in oracle environment from the oracle files through the SAS environment */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Select * from connection to oracle (select * from lab);

Disconnect from oracle;

Quit;

/* To report required variables */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Select * from connection to oracle (select stno, unitsfrom lab);

Disconnect from oracle;

Quit;

/* To import the data from oracle table to SAS */

Eg:

Proc sql;

Connect to oracle

(user = scott password = tiger path = ‘Ora’);

Create view lab 1 as

Select * from connection to oracle (select * from lab);

Disconnect from oracle;

Quit;

Primary, reference, foreign key are called as surggate key in data ware housing

Access procedure:

Belongs to old technology . using access procedure, we can access the data from old technology external files (94, 95 yr). using access procedure, we can access the data from external files and stores in views only views are called access views.

Views are two types

1. Native view
2. Data set view (data set)
3. Sql view(sql)
4. Interface view – access view (access procedure)

 

To access the data from excel file:

Get names statement:

Default value of the get names statement is ‘NO’ in access procedure.

Note:

Based on one access descriptor we can create multiple views

Eg:

Proc access dbms = xls;

Create work. Demo. Access;

Path = ‘d:\old.xls’;

Get names = yes;

Create work. Demo2. View;

Select subid age;

Create work. Demo3. View;

Subset where gender = 1;

Select all;

Run;

NOTE:

If we create any view use with access procedure, these views are called access views

Worksheet statement:

Can be used to get the data from the required sheet

• In old technology $ dollar symbol is not needed.

Note;

If you want to do any changes in views, first of all we will do changes in access descriptor changing variables names and format changes.

Eg:

Proc access dbms == xlx;

Create work. Demo4. Access;

Path = ‘d:\ old.xls’;

Worksheet = ‘sheet 2’;

Rename.varo =stno

Var2 = gender

Var3 = color’

Create work. Demo4. View;

Select all;

Run;

Proc print. Data = work. Demo 4;

Interaction with oracle environment:

Eg:

Proc access dbms = oracle;

Create work. One.  Access;

User = scott;

Password = tiger;

Path = ‘ora’;

Table = ‘clinical’;

Create sas user. Demog2. View;

Select all;

Create sasuser. Demog2. View;

Select 1,3;

Create sasuser. Demog3. View;

Subset where pname

Is not null;

Select all;

Run;

Number                                               character

Missing                                                 null

Not missing                                        not null

To export the data into oracle tables:

Db load procedure:

Using database load procedure, we can export the data from SAS environment to require data base.

Eg;

Data load;

Input-pid test $ units cdate;

In format cdate date9;

Format cdate date9;

Cardas ;

100         HR          78           12 JAN 2007

100         DBP        90           12 JAN 2007

100         SBP        145         12 JAN  2007

;

Data lab 2;

Input pid test $ units c date;

Informat c date date9;

Format cdate date9;

Cards;

101         hr            74           13 jan 2007

101         dbp        90           13 jan 2007

101         sbp         154         13 jan2007

Load statement:

Can be used to load the data in required database table

/* To load data in oracle table */            

Eg:

Proc db load dbms = oracle

Data = lab 1;

User  = scott;

Password = tiger;

Path = ‘ora’;

Table = ‘lab’;

Load;

Run;

Append option:

Can be used to append the data in oracle tables from the SAS datasets

Eg:

Proc db load dbms = oracle

Data = lab 2;

User  = scott;

Password = tiger;

Path = ‘ora’;

Table = ‘lab’;

Load;

Run;

Eg:

Data = lab 3;

Input pid test $ units cdate e time;

Informat c date date9;

C time time 8;

Format c date date 9

C time time 8;

Cards;

102         hr            74           14 JAN 2007        12: 23: 23

102         dbp        90           14 JAN 2007        13: 23: 12

102         sbp         154         14 JAN  2007       14: 12: 12

103         hr            74           14 JAN 2007        12: 23: 23

103         dbp        90           14 JAN 2007        13: 23: 12

103         sbp         154         14 JAN  2007       14: 12: 12

Db load procedure supporting to all options except rename option

/* To export required variable in required time */

Eg:

Proc db load dbms = oracle

Data = lab 3 ( drop etime rename = (date = c date) where = (pid = 103)) append;

User = scott;

Password = tiger;

Path = ‘ora’

Table = lab;

Load;

Run;

/* Extraction*/

Proc import

Data file = ‘d:\ suni.xls’

Out = demo dbms = excel replace;

Sheet = ‘sheet 1 $’;

Quit;

Data file = ‘d:\ suni.xls’

Out = demo dbms = excel replace;

Sheet = ‘sheet 2 $’;

Quit;

/* Create table in oracle environment */

Proc sql;

Connect to oracle

(user = sas password = sas path =’ora’);

Execute ((create table demo (pid number primary key, age number, rece char(12), color char (12)))by oracle;

Disconnect from oracle’

Quit;

Proc sql;

Connect to oracle

(user = sas password = sas path =’ora’);

Execute ((create table lab (pid number, test char(12), units number, foreign key(pid)references demo)) by oracle;

Disconnect from oracle’

Quit;

/* Loading*/

Proc db load dbms = oracle

Data = demo 1 append;

User = sas;

Password = sas;

Path = ‘ora’;

Table = demo;

Load;

Run;

Proc db load dbms = oracle

Data = lab 1 append;

User = sas;

Password = sas;

Path = ‘ora’;

Table = lab;

Load;

Run;

For indepth knowledge on SAS, click on below

• Normalization in SAS
• SQL Aggregations & DataSet(Row & colummn operations) in SAS
• SQL Statements in SAS
• Working with Macro Variable in SAS
• SAS Interview Questions & Answers