A Complete PROC SQL SAS Guide

What is SAS SQL

SAS SQL — PROC SQL SAS

The procedure PROC SQL is used to process the SQL statements. This procedure can not only give back the result of an SQL query, it can also create SAS tables & variables.

The syntax of PROC SQL SAS-

PROC SQL: calls the SAS SQL procedure
SELECT: specifies the column(s) (variables) to be selected
FROM: specifies the table(s) (data sets) to be queried
WHERE: subsets the data based on a condition
GROUP BY: classifies the data into groups based on the specified column(s)
ORDER BY: sorts the resulting rows (observations) by the specified column(s)
QUIT: ends the PROC SQL procedure

SQL vs SAS

PROC SQL STATEMENTS

The statements used in SAS SQL for creating, updating and viewing datasets differ from the statements. Here is a summary of the differences. We will be looking at the important ones from this table.

PROC SQL STATEMENTS

a. Display Data Set

The most basic usage of PROC SQL is to display (or print) all variables (columns) and observations (rows) from a given dataset in the SAS Results window.

Read more about SAS Datasets and Types

Using the SASHELP.CLASS dataset with Base SAS code, you can see here how to print the entire dataset to the results window using the PRINT procedure:

1. proc print data=sashelp.class;
2. run;

results can be obtained using a SELECT statement. To display all columns in the Results window, an asterisk (*) is used following a SELECT to indicate that you would like to keep all variables (columns) in the output. A call to PROC SQL SAS is concluded with a semi-colon, followed by a QUIT statement and another semi-colon as shown here:

1. proc sql;
2. select * from sashelp.class
3. ;
4. quit;

Display Data Set in PROC SQL SAS

For datasets with a large number of variables, it may be preferable to only view a subset of those variables. With Proc Print, this would be accomplished with a VAR statement. Here, the VAR statement is used to print only the Name and Age variables from SASHELP.CLASS:

Read about How to Merge Data Sets in SAS

1. proc print data=sashelp.class;
2. var name age;
3. run;
4. proc sql;
5. select name,age from sashelp.class;
6. quit;

Displaying Data Set in PROC SQL SAS

b. Creating Dataset from Existing Data

Similar to the Data Step in base SAS programming, PROC SQL can also be used to create new datasets from existing data. To create a new dataset in the WORK library called class_new, which contains all the variables and observations from SASHELP.CLASS, the Base SAS data step is used along with a SET statement as follows:

1. Data class_new;
2. Set sashelp.class;
3. Run;

The equivalent output dataset is produced by using CREATE TABLE and AS statements before the SELECT statement:

1. proc sql;
2. create table class_new as
3. select * from sashelp.class
4. ;
5. quit;

Creating Dataset from Existing Data

c. The WHERE Clause

SAS uses where clause to subset data, that is, to get only the values that we want and discard the rest of the values. Example, if we want only people whose gender is male, then we will specify it in a where clause.

Let’s explore SAS Input Method or Statements in detail

We have created a dataset called CLASSFIT_MALES which contains all variables from CLASSFIT but only those records where Sex is ‘M’ (i.e. only those records for males).:

1. data classfit_males;
2. set sashelp.classfit;
3. where sex = ‘M’;
4. run;
5. proc sql;
6. select * from classfit_males where sex=’M’
7. ;
8. quit;

WHERE Clause — SAS SQL

d. Updating the Dataset

Using the CLASS dataset as an example, say that you would like to know what the height is for each person in both inches and centimeters. Using a single select statement, the name variable is kept, the current height variable can be renamed to height_inches, and a new height variable called height_cm can be created by multiplying the height in inches by 2.54 to convert to centimeters.

1. proc sql;
2. create table class_heights as
3. select name, height as height_inches, (height*2.54) as height_cm from sashelp.class;
4. quit;

SAS SQL

e. Deleting Rows from the Dataset

Just like we saw some of the above statements, the delete statement inside the PROC SQL SAS deletes rows of our choice.

1. proc sql;
2. delete
3. from sashelp.cars
4. where salary>200
5. ;
6. quit;

This was all on the SAS SQL Tutorial. Hope you like our explanation.

Conclusion

Hence, today we looked at what is SAS SQL and how PROC SQL statements can be used instead of SAS statements to apply operations on our dataset. There is not much difference between the statements. We also saw how to view entire data, change it, view data of our interest, delete it and also change it temporarily in SAS SQL. Hope you all liked it. Stay tuned for more.
Furthermore, if you have any query, feel free to ask in a comment box.