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:

1. Adding new column
2. Adding or dropping required column for the existed table.
3. Assign the constraints
4. 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

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