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.
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.
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;
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;
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:
/* 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
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
