SQL Procedure

Example 5: Using an Inner Join

Features:	INNER JOIN keywords
Table names:	Sql.Oilprod Sql.Oilrsrvs

Input Tables

This example uses an inner join to create a new table that contains data columns from two tables. The data is organized by the descending order of the values in the Production column.

Note: The source tables are available in a ZIP file at https://support.sas.com/en/software/base-sas-support.html#documentation. Under the “Related Documentation” topic on that page, look for Example Tables for SAS SQL Procedure User’s Guide. Download the ZIP file and extract the data files to a location that is accessible by SAS.

libname libref 'SAS-library';
proc sql ;
   select p.country, barrelsperday 'Production', barrels 'Reserves' 
      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country
   order by barrelsperday desc;

libname libref 'SAS-library';
proc sql ;
   select p.country, barrelsperday 'Production', barrels 'Reserves' 
      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country
   order by barrelsperday desc;

Table Created with an Inner Join

This image contains a table that has three columns. The column names are Country, Production, and Reserves.

Program

libname libref 'SAS-library';
proc sql ;
   select p.country, barrelsperday 'Production', barrels 'Reserves'
      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country
   order by barrelsperday desc;

libname libref 'SAS-library';
proc sql ;
   select p.country, barrelsperday 'Production', barrels 'Reserves'
      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country
   order by barrelsperday desc;

Program Description

Declare the library.Create a LIBREF for the library that contains the downloaded files from the ZIP file.

libname libref 'SAS-library';

Select the tables and specify headers for the columns.Assign Production as the new title for the BarrelsPerDay column, and assign Reserves as the new title for the Barrels column.

proc sql ;
   select p.country, barrelsperday 'Production', barrels 'Reserves'

Assign aliases for the tables, and use an inner join.Assign the table Sql.Oilprod the alias P, and assign the table Sql.Oilrsrvs the alias R. Use the INNER JOIN keywords to extract the columns.

      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country

Assign the order of the data.DESC specifies that the data should be organized by the descending order of the values in the Production column.

   order by barrelsperday desc;

Last updated: April 9, 2026