PROC SQL FOR BEGINNERS - HOW TO USE SQL IN SAS

PROC SQL SAS

SAS offers support to relational databases by using SQL queries within SAS Program using Proc SQL. Procedure PROC SQL is used to evaluate and run the SQL statements and also for creating New Dataset/table out of written query, Also it can create new Variables/Columns in Dataset(s).

SQL is one of the Top 5 Programming Languages for Data Analysis, The advantage of PROC SQL over SQL is - we can use SQL along with Base SAS programming logic which can help us to solve more complex data analysis problem very easily.

Lets see how we can utilize PROC SQL to write SQL in SAS - 

Syntax for Proc SQL  - 

PROC SQL;
SELECT Columns
FROM TABLE
<WHERE Condition
GROUP BY Columns
Having Condition
Order by Columns;>

QUIT;

The order of above Select Statement should always be like  -

1. Select
2. From
3. Where
4. Group by
5. Having
6. Order by

Except PROC statement, SELECT Statement, FROM Statement and QUIT statement everything else is optional.

Lets see some easy examples for PROC SQL  - 

1. Write a program using PROC SQL to Select all Rows from Cars dataset of SASHELP Library

Proc SQL;

Select * From SAShelp.Cars;

quit;

Here * is use to select All Variables of Cars Dataset.

2. Write a program using PROC SQL to Select Make, Model, Type and MSRP columns and contains all Rows from Cars dataset of SASHELP Library.

Proc SQL;

Select Make, Model, Type, MSRP From SAShelp.Cars;

quit;

 Here we just replaced * with Columns which we want to get in our report.

3. Write a program using PROC SQL to Select all cars which have MSRP greater than $ 100000.

Proc SQL;

Select * From SAShelp.Cars

Where MSRP >100000;

quit;

 Here we use the Where Condition to filter data as per requirement.

4. Write a program using PROC SQL to get the Sum of Invoice for All Make(Grouped together)

Proc SQL;
	Select Make,Sum(invoice) as Invoice_Total From SASHELP.Cars
	Group by Make;
Quit;

 Proc SQL;
Select Make,Sum(invoice) as Invoice_Total From SASHELP.Cars
Group by Make;

Quit;

Here we use Group by along with SUM (Aggregate Function) to get the some of every Make(grouped).

 

5. Write a program using PROC SQL to get Those Makes whose Sum of Invoice is greater than $700000

Proc SQL;

Select Make,Sum(invoice) as Invoice_Total From SASHELP.Cars

Group by Make

Having Invoice_Total > 700000;

Quit;

Here we use Having option to Apply condition on Grouped Column.

6. Write a program using PROC SQL to get all cars sorted by Make and Then model and Whose origin='Asia' and DriveTrain='All'.

Proc SQL;

Select * From SAShelp.Cars

where origin='Asia' and DriveTrain='All'

order by Make, Model;

quit;

Here we use Order by Statement to Sort Make and Model in Ascending order, also used where Condition to Filter Orgin and Drive train.

 MORE PROC SQL EXAMPLES