SQL Statements in SAS
SQL
- It is called as structured query language(or)SQL concept.
- Using SQL concept, we can handle any database.
- Using SQL concept, we can generate result in SAS environment or outside of the environment.
- SQL concept mainly running based on 4 concepts.
- DDL (Data Definition Language)
- DML(Data Manipulation Language)
- DCL(Data Controlling Language)
- Query language.
DDL:
Using this concept, we can create a table with variables without observations(null data set)using procedure and data set block.
DML:
Using this concept, we can insert the data in existed table, update data values, delete the observation from existed table.
DCL:
We can control data process.
Query Language:
Using with query language, we can retrieve the data for reporting and storage.
- To create a table use with SQL block , SQL block start with proc sql and ends with quit statement.
Create statement:
It can be used to create the tables with variables and without observations.
Insert statement:
It can be used to insert the data in existed table. Insert statement is working based on value clause or set clause.
- Value clause is working based on variable position.
- Set clause is working based on variable name.
Select statement:
It is a query statement, it can be used to retrieve the data for reporting and storage.
/* create a table with SQL block*/
Ex:
Proc sql;
Create table demo(pid num, age num, gender char);
Insert into demo values (100, 23,’male’)
values (101, 34, ’female’)
values (102, 45, ’female’)
select * from demo;
quit;
Learn the core features of SAS and become master with our expertise SAS Training.
Ex:
Proc sql;
Create table medi(stno num, drug char, sdate num, stime num, edtime num);
Insert into medi values(100,’5mg’,’12jan2003’d,’12:23:23’t,’17dec2003:10:23:23’dt);
Select stno, drug,
Sdate format = ddmmyy10.,
Stime format = timeampm12.,
Edtime format = date time18.
From medi;
Quit;
- If we write format statement in query statements, these statements are temporary.
- If we write format in create statement these statements are permanenet.
Ex:
Proc sql;
Create table medi(stno num, drug char, sdate num, stime num, edtime num);
Insert into medi values(100,’5mg’,’12jan2003’d,’12:23:23’t,’17dec2003:10:23:23’dt);
Select stno, drug,
Sdate format = ddmmyy10.,
Stime format = timeampm12.,
Edtime format = date time18.);
/* to insert the data with set clause */
Proc sql;
Create table lab (pid num, test char, units num);
Insert into lab
Set pid =100,test =’hr’, units =78
Set pid =100,test =’dbp’, units =89
Set pid =100,test =’sbp’, units =145;
Select * from lab;
Quit;
Order by clause:
It can be used to report the data in ascending or descending order. Default descending order, order by clause can be written in select statement. Order by clause requires variables names or variable position.
Ex:
Proc sql;
Select * from sashelp. class
Order by age;
Quit;
Ex:
Proc sql;
Select * from sashelp. class
Order by age desc;
Quit;
/* To create a table using query statement */
Ex:
Proc sql;
Create table class as
Select * from sashelp. class
Order by age desc;
Quit;
- SQL block is more efficient for reporting comparative to data set block. Sql block is both reporting and storage. data set block is only for storage.
Where clause:
It can be used to create a subset of data for reporting and storage.
/* reporting */
Ex:
Proc sql;
Select * from sashelp. class
Where age >= 14;
Quit;
/* storage */
Ex:
Proc sql;
Create table class2 as
Select * from sashelp. class
Where age >= 14;
Quit;
/* To report age>=14 sub and to generate report in descending based on age variable*/
Ex:
Proc sql;
Select * from sashelp. class
Where age >= 14 Order by age desc;
Quit;
/* To manipulate data for reporting*/
Ex:
Proc sql;
Select eid, salary +1000 as salary, sale from emp;
Quit;
/* To create new variables for reporting */
Ex:
Proc sql;
Select * , salary +1000 as nsalary from emp;
Quit;
To do data manipulation based on condition:
If we want to manipulate the data based pn condition, in this case we will use case when then else clause -> closed with end statement.
Ex:
Proc sql;
Select * , salary +case
When sale ge 500 then 2000
Else 1000 end
As nsalary from emp;
Quit;
When condition are more, then we will use when clause
Ex:
Proc sql;
Select * , salary +case
When sale ge 500 then 2000
When sale ge 400 and sale lt 500 then 1500
Else 1000 end
As nsalary from emp;
Quit;
/* to create a new variable in existed data set*/
Update statement :
It can be used to modify the data values in existed variables
Syntax:
Update <table name> set <variable name> = <expressions>;
Alter statement:
It can be used to modify the existed table.
Modifications:
- Adding new column
- Adding or dropping required column for the existed table.
- Assign the constraints
- Delete the constraints
/* Add new column*/
Ex:
Proc sql;
Alter table emp
Add nsalary num
Format =comma 12.;
Quit;
/* Adding values in new column*/
Ex:
Proc sql;
Update emp set nsalary= salary+case
When sale ge 500 then 2000
When sale ge 400 and sale lt 500
Then 1500
Else 1000 end;
Quit;
Note:
Using alter statement, we can add multiple column (or) variables(or) drop multiple columns or variables at a time.
/* drop column */
Ex:
Proc sql;
Alter table emp
Drop salary;
Quit;
Alter table<table name> add <variable name><data type><format>;
Syntax:
Alter table <table name> drop<variable name>;
| sbp | drug | Daily dose |
| >= 1!=0 | 15mg | 3 |
| >=150 and <170 | 10mg | 2 |
| <=150 | 5mg | 3 |
Data medi;
Input pid sbp;
Cards;
100 156
101 176
102 140
103 180
104 145
105 167
;
Proc sql;
Select * , case
When sbp >= 170 then ‘15mg’
When sbp >= 150and
Sbp <170 then ‘10mg’
Else ‘5mg’ end
As drug , case
Where sbp >= 170 then 3
When sbp >= 150 and
Sbp <170 then 2
Else 3 end as
Daily dose
From medi;
Quit;
/* new variable creation for storage */
Ex:
Proc sql;
Alter table medi
Add drug char, dailydose num;
Quit;
/* Loading values in drug*/
Ex:
Proc sql;
Update medi set
Drug =case
When sbp >= 170 then ‘15mg’
When sbp >=150 and
Sbp<170 then ‘10mg’
Else ‘5mg’ end;
Quit;
/* loading valies in daily dose*/
Ex;
Proc sql;
Update medi set
Daily dose =case
When sbp >= 170 then 3
When sbp >=150 and
Sbp<170 then 2
Else 3 end;
Quit;
For indepth knowledge on SAS, click on below