Normalization in SAS

NORMALIZATION:

This is one of the logical model Using this model, we can create data marts or data sets related to business requirements.

Pass through facility

This concept developed by two SAS modules concept one is sql, and other is access. It does not have over right capability. Using with pass through facility, we can handle other database from the SAS environment.

RESULT:

1. Create table in other database environment
2. Insert data in other database tables
3. Update the data values in other database tables
4. Modify the tables (add variable, assign, drop constraints)
5. Delete the observations

ACCESS ENVIRONMENT:                       

Create a table in access environment through SAS

CONNECT TO STATEMENT:

Use with connect to statement; we can interact with other database environment.

EXECUTE STATEMENT:

Using with execute statement; we can execute required completion coding in other environment.

Eg:

Proc sql;

Connect to access (path = ‘d:\ base.mdb’);

Execute (create table demo (pi dint, gender char, age int)) by access;

Execute (insert into demo values (100,’female’, 45) by access;

Execute (insert into demo values (101,’male’, 45) by access;

Disconnect from access;

Quit;

Interested in mastering SAS Developer? Enroll now for FREE demo on SAS Training

/* update the data values in table */

Proc sql;

Connect to access

(Path =‘d:\base.mdb’);

Execute (update emp.set salary = salary +2000) by access;

Disconnect from access;

Quit;

/* modify the table */

Proc sql;

Connect to access;

(Path = ‘d:\base.mdb’);

Execute (after table emp add new sal int) by access;

Execute (update emp set new sal = salary + 3000) by access;

Disconnect from access;

Quit;

/* drop the column*/

Proc sql;

Connect to access;

(Path = ‘d:\ base.mdb’);

Execute (after table emp drop salary) by access;

Disconnect from access;

Quit;

/* delete observations */

Proc sql;

Connect to access

(Path = ‘d:\base.mdb’);

Execute (delete from emp where gender = ‘Female’)

By access;

Disconnect from access;

Quit;

By using the drop option, we can delete only variables in a table, but by drop statement we can telete table.

Eg:

Proc sql;

Connect to access

(Path = ‘d:\ base.mdb);

Execute (drop table emp);

By access;

Disconnect from access;

Quit;

To report external source information in SAS, without creation SAS file:

Eg:

Proc sql;

Connect to access

(path =’d:\base.mdb’);

Select * from connection to access (select *from rawdata),

Disconnect from access;

Quit;

/* To report required variables */

Eg:

Proc sql;

Connect to access

(Path = ‘d:\base.mdb’);

Select *from connection to access (select name, age from raw data where age>= 13);

Disconnect from access;

Quit:

/* To importing the data */

Eg:

Proc sql;

Connect to access

(Path = ‘d:\base.mdb’);

Select *from connection to access (select name, age from raw data where age>= 13);

Disconnect from access;

Quit:

Using with pass through facility we can access the data any type of data base except txt and stores in data sets and views

Interact with excel environment:

/* To create a sheet in excel environment and insert the data*/

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ data.xls’);

Execute (create table adevent (stno int, adtype char))

By excel;

Execute (insert into advent values (123, ‘headache’)) by excel;

Execute (insert into adevent values (131,’eyedis’)) by excel;

Disconnect from excel;

Quit;

To modify the sheet we can create select update the sheet through pass through facility but we cannot modify, delete the table, delete the observation through pass through facility with excel.

/* Reporting */

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql-demo.xls’);

Select *from connect to excel (select * from [demo 1 $]);

Disconnect from excel;

Quit;

/* To report required variables*/

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql-demo.xls’);

Select *from connect to excel (select subid, age from [demo 1 $]);

Disconnect from excel;

Quit;

/* To report required observations based on range */

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql-demo.xls’);

Select *from connect to excel (select * fro.subid, age from [demo 1 $ a1:d10]);

Disconnect from excel;

Quit;

Header option:

Using header option, we can control variable accessing when access the data from middle of the excel file.

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql_demo.xls’ header _ no);

Select *from connect to excel (select * from [demo 1 $ a10:d 20]);

Disconnect from excel;

Quit;

/* To report part of the data based on condition */

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql_demo.xls’);

Select *from connect to excel (select * from [demo 1 $ where age > =20]);

Disconnect from excel;

Quit;

MIXED OPTION:

Using mixed option, we accessed mixed data from excel. Default is ‘no’. if we want to give mixed data we must give ‘yes’

/* To repeat mixed data */

Eg:

Proc sql;

Connect to excel

(Path = ‘d:\ sql-demo.xls’ mixed = yes);

Select *from connect to excel (select * from [sheet 2 $]);

Disconnect from excel;

Quit;

Note:  mixed data while repeating time, SAS takes all data in character format

For indepth knowledge on SAS, click on below

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