• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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>;

sbpdrugDaily dose
>= 1!=015mg3
>=150 and <17010mg2
<=1505mg3

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

Summary
Review Date
Reviewed Item
SQL Statements in SAS
Author Rating
4

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL Statements in SAS"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.