Lesson 6, Part 1: PROC SQL Basics¶

Lund, Pete. An Introduction to SQL in SAS®. SUGI 30

Jepson, Brett. (2019). Sometimes SQL Really Is Better: A Beginner's Guide to SQL Coding for DATA Step Users

PROC SQL is a powerful data manipulation and query tool. You can use it to:¶
•	Manipulate data 
•	Subset data/tables
•	Sort data/tables (also by PROC SORT)
•	Aggregate data/tables (also by PROC MEANS and PROC SUMMARY)
•	Perform full join, inner join, left join, right join
•	Perform subquery
•	Print results (also by PROC PRINT)
•	Retrieve metadata (also by PROC CONTENTS)

You can accomplish many of the tasks with only six clauses and one keyword

•	SELECT columns (variables) that you want
•	FROM tables (datasets) that you want
•	WHERE row (observation) conditions that must be met
•	GROUP BY summarize by these columns
•	HAVING summary conditions that must be met
•	ORDER BY sort by these columns

The keyword ON, which goes after the FROM clause and joins conditions that must be met.

SELECT statement and its clauses¶

  • The SELECT statement retrieves and displays data.
  • A SELECT statement can be used to query up to 256 tables.
  • The results of the SELECT statement are written to the default output destination.
  • The SELECT clause specifies the columns and column order.
  • An asterisk in the SELECT caluse means all columns.
  • The FROM clause specifies the data sources.

The following SQL code is equivalent to the PROC PRINT step.

An asterisk in the SELECT statement of PROC SQL tells SAS to print all variables from the input data set.

In [10]:
proc sql inobs=5;
title 'Only 5 observations read from the input data set';
select * 
 from sashelp.class;
quit;
title;
Out[10]:
SAS Output

Only 5 observations read from the input data set

Name Sex Age Height Weight
Alfred M 14 69 112.5
Alice F 13 56.5 84
Barbara F 13 65.3 98
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5

In the SELECT clause of PROC SQL, a comma is used between two variables while a space is used between variables.

The FORAMT attribute of the WEIGHT variable is set within its specifications.

In [1]:
proc sql inobs=4;
select name, weight format = 6.1
From sashelp.class;
quit;

SAS Connection established. Subprocess id is 9680
Out[1]:
SAS Output

The SAS System

Name Weight
Alfred 112.5
Alice 84.0
Barbara 98.0
Carol 102.5

Calculating Columns and Column Alias in PROC SQL¶

"A column alias is a temporary, alternate name for a column. Aliases are specified in the SELECT clause to name or rename columns so that the result table is clearer or easier to read" (SAS Documentation).

In [1]:
proc sql inobs=5;
title 'Only 5 observations read from the input data set';
select name, weight format = 6.1,
round((weight*703.069)/(height**2), .01) as bmi
From sashelp.class;
quit;
title;
SAS Output

Only 5 observations read from the input data set

Name Weight bmi
Alfred 112.5 16.61
Alice 84.0 18.5
Barbara 98.0 16.16
Carol 102.5 18.27
Henry 102.5 17.87

Subsetting with the WHERE Clause¶

  • The WHERE clause can be used to specifiy a condition that the data must satisfy before being selected. The WHERE clause in PROC SQL uses the variable that is already included in the input data set and applies to the input data set, similar to the WHERE statement in DATA step coding.

  • On the other hand, the HAVING clause works on a subset based on the output data set rather than the input data set (see the example code later).

In [7]:
proc sql inobs=5;
title 'Only 5 observations read from the input data set';
create table class as
select name, sex, weight format=6.1, height format=6.1,
round((weight*703.069)/(height**2), .1) as bmi
From sashelp.class;
select * 
 from class
 where sex = 'M';
quit;
title;
Out[7]:
SAS Output

Only 5 observations read from the input data set

Name Sex Weight Height bmi
Alfred M 112.5 69.0 16.6
Henry M 102.5 63.5 17.9

Subsetting with Calculated Values in the WHERE Clause¶

In SQL, if you specify a derived variable in the ORDER BY, GROUP BY or HAVING clause, you must precede the variable name with the word CALCULATED.

In [10]:
proc sql;
select name, sex, weight format=6.1, height format=6.1,
round((weight*703.069)/(height**2), .1) as bmi
From sashelp.class
 where calculated bmi ge 20;
quit;
Out[10]:
SAS Output

The SAS System

Name Sex Weight Height bmi
Janet F 112.5 62.5 20.2
John M 99.5 59.0 20.1
Philip M 150.0 72.0 20.3
Robert M 128.0 64.8 21.4
Ronald M 133.0 67.0 20.8

GROUP BY Clause¶

  • classifies the data into groups based on the values of one or more columns
  • calculates statistics for each unique value of the grouping columns

In the following code, the GROUP BY clause classifies the data based on the values of the MAKE column and calculate the mean INVOICE price for each unique value of the grouping column (i.e., MAKE).

The ORDER BY clause order the results in descending order.

In [3]:
*Ex6_sql_moreCode.sas (Part 13);
title 'Only 5 observations ptinted';
proc sql outobs=5;
 select make, 
  mean(invoice) as mean_invoice_price format=dollar8.
   From sashelp.cars
   group by make
   order by calculated mean_invoice_price desc;
quit;
title;
Out[3]:
SAS Output

Only 5 observations ptinted

Make mean_invoice_price
Porsche $73,663
Mercedes-Benz $56,453
Jaguar $56,098
Cadillac $46,427
Hummer $45,815

HAVING Caluse and CALCULATED Keyword¶

The HAVING clause works on a subset based on the output data set rather than the input data set.

In the following code, the HAVING clause subsets groups based on the expression value. Here we use a column alias to refer to a calculated value. and hence, use the CALCULATED keyword with the alias to inform PROC SQL that the value is calculated within the query (SAS Documentation).

In [29]:
*Ex6_sql_moreCode.sas (Part 14); 
proc sql ; 
select name
       ,pop
       ,pop/sum(pop) as percent_pop 
          format=percent7.2
  from  sashelp.demographics
   having calculated percent_pop >0.02
   order by percent_pop desc;
quit;
Out[29]:
SAS Output
GLC Country Name Population (2005) percent_pop
CHINA 1,323,344,591 19.8%
INDIA 1,103,370,802 16.5%
UNITED STATES 298,212,895 4.47%
INDONESIA 222,781,487 3.34%
BRAZIL 186,404,913 2.79%
PAKISTAN 157,935,075 2.37%
RUSSIA 143,201,572 2.15%
BANGLADESH 141,822,276 2.13%

HAVING Clause¶

  • subsets groups based on the expression value.
  • is processed after the GROUP BY clause and determines which groups are displayed.

The HAVING clause works on a subset based on the output data set rather than the input data set

In [11]:
proc sql;
select make 
       ,count(make) as N
       ,Avg(invoice) as avg_invoice format=dollar7.
From sashelp.cars
 group by make
 having Avg(invoice) ge 50000
 order by avg_invoice desc;
quit;
Out[11]:
SAS Output

The SAS System

Make N avg_invoice
Porsche 7 $73,663
Mercedes-Benz 26 $56,453
Jaguar 12 $56,098

Use the INSERT statement with the VALUE clause to add rows to a data table¶

In [27]:
*** Acknowlegements: Some of the code idea  (INSERT, VALUE, SET,  
      and UPDATE clauses) is obtained from Martha Messineo (2017);

*** Use the INSERT clause to create a data table;
options nocenter nonumber nodate nonotes nosource;
proc sql;
Create table have(ID varchar(6), stype varchar(13), Score float);
Insert into have(id, stype, score)
    Values('S00003', 'Graduate', 19)
    Values('S00002', 'Graduate', 14)
    Values('S00007', 'Graduate', 18)
    Values('S00008', 'Graduate', 16)
    Values('M00004', 'Undergraduate', 13)
    Values('E00005', 'Undergraduate', 18)
    Values('M00022', 'Undergraduate', 17)
    Values('E00035', 'Undergraduate', 16)
    ;
 title;   
 select * from have; 
quit;
Out[27]:
SAS Output
ID stype Score
S00003 Graduate 19
S00002 Graduate 14
S00007 Graduate 18
S00008 Graduate 16
M00004 Undergraduate 13
E00005 Undergraduate 18
M00022 Undergraduate 17
E00035 Undergraduate 16
In [ ]:
*Ex6_sql_moreCode.sas (Part 3);
proc sql;
create table class AS
select * 
  from sashelp.class;
Insert into class
  values('Amy', 'F', 14,64, 107);
 select *
    from class;
quit;
title;
In [15]:
*Ex6_sql_moreCode.sas (Part 6);
*** Use the UPDATE clause to conditionally set a value of a particular
*** variable to another value;
title 'Only 5 observations (rows) read from the input data set';
proc sql inobs=5;
  create table new_class AS
   select *
   from sashelp.class;
   update new_class
    set age=age+1
    where sex='F';
    select *
    from new_class;
quit;
title;
Out[15]:
SAS Output

Only 5 observations (rows) read from the input data set

Name Sex Age Height Weight
Alfred M 14 69 112.5
Alice F 14 56.5 84
Barbara F 14 65.3 98
Carol F 15 62.8 102.5
Henry M 14 63.5 102.5
In [ ]:
*Ex6_sql_moreCode.sas (Part 7);
*** The following two code blocks (PROC PRINT and PROC SQL) 
    provide the same results;
title1 'Listing from PROC PRINT';
proc print data=sashelp.cars noobs;
  var make type;
 where make='BMW';
run;
In [ ]:
*Ex6_sql_moreCode.sas (Part 8);
proc sql;
title1 'Query from from the SELECT clause with PROC SQL';
 select make, type
 From sashelp.cars
 where make='BMW';
quit;
title1;

The following two code blocks (DATA step and PROC SQL) provide the same results. There are other DATA step solutions (not shown here);

In [1]:
*Ex6_sql_moreCode.sas (Part 9);
data _null_;
  set sashelp.cars end=eof;
  count+1;
  if eof then putlog count=;
run;

5                                                          The SAS System                            13:53 Friday, February 21, 2025

24         ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
24       ! ods graphics on / outputfmt=png;
NOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1
25         
26         *Ex6_sql_moreCode.sas (Part 9);
27         data _null_;
28           set sashelp.cars end=eof;
29           count+1;
30           if eof then putlog count=;
31         run;

count=428
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds
      

32         
33         
34         ods html5 (id=saspy_internal) close;ods listing;
35         

6                                                          The SAS System                            13:53 Friday, February 21, 2025

36
In [2]:
*Ex6_sql_moreCode.sas (Part 10);
proc sql;
 select count(*) as total_rows
 From sashelp.cars;
quit;
SAS Output

The SAS System

total_rows
428

*** The following two code blocks (PROC SORT/PROC PRINT and PROC SQL) provide the same results);

In [ ]:
*Ex6_sql_moreCode.sas (Part 11);
proc sort data=sashelp.cars 
       (where=(make='BMW')) nodupkey
  out=distinct_make_type (keep=make type);
  by make type;
run;
proc print data=distinct_make_type noobs;
run;
In [ ]:
*Ex6_sql_moreCode.sas (Part 12);
proc sql;
 select distinct make, type
 From sashelp.cars
 where make='BMW'
 order by type;
quit;

In the following code, the GROUP BY clause classifies the data based on the values of the MAKE column and calculate the mean INVOICE price for each unique value of the grouping column (i.e., MAKE). The ORDER BY clause order the results in descending order;

In [ ]:
*Ex6_sql_moreCode.sas (Part 13);
proc sql;
 select make, 
  mean(invoice) as mean_invoice_price format=comma8.
   From sashelp.cars
   group by make
   order by mean_invoice_price desc;
quit;

In the following code, the HAVING clause subsets groups based on the expression value. Here we use a column alias to refer to a calculated value. and hence, use the CALCULATED keyword with the alias to inform PROC SQL that the value is calculated within the query (SAS Documentation);

In [ ]:
*Ex6_sql_moreCode.sas (Part 14); 
proc sql ; 
select name
       ,pop
       ,pop/sum(pop) as percent_pop 
          format=percent7.2
	   from  sashelp.demographics
	   having calculated percent_pop >0.02
	   order by percent_pop desc;
quit;

SAS Note Sample 36898: Count the distinct values of a variable

Counting the number of occurrences for a grouped variable

In [13]:
ods html close;
options nocenter nonumber nodate nosource nonotes;
DATA D2;
 INPUT CustID Month $ purchased_amount;
 DATALINES;
 11 Feb 288.2 
 12 Feb 221.7 
 13 Feb 274.4  
 14 Feb 222.9
;

The SAS System

E3969440A681A2408885998500000015
In [14]:
data comb0;
 set D1 D2;
run;
proc print;
run;
SAS Output

The SAS System

Obs CustID Month purchased_amount
1 11 Jan 237.4
2 12 Jan 249.2
3 13 Jan 227.7
4 11 Feb 288.2
5 12 Feb 221.7
6 13 Feb 274.4
7 14 Feb 222.9
In [8]:
options nocenter nonumber nodate;
proc sql;
 create table concat_sql as
 select * from D1
   union 
 select * from D2
 order by Month desc;
 title1 'Vertical Joining Using PROC SQL';
 select * from concat_sql;
quit;
Out[8]:
SAS Output

Vertical Joining Using PROC SQL

CustID Month purchased_amount
11 Jan 237.4
12 Jan 249.2
13 Jan 227.7
11 Feb 288.2
12 Feb 221.7

UNION operator with PROC SQL¶

The default behavior of the UNION operator is that the duplicate rows are removed from the final results. Due to ALL modifier,duplicate results are not removed.

With the UNION operator, column names in the final result set are determined by the first result set.

However, due to CORRESPONDING modifier, columns are matched by name and nonmatching columns are removed from the intermediate result set (SAS Documentation).

In [13]:
options nocenter nonumber nodate;
proc sql;
 create table concat_sql_i as
 select * from D1
   union 
 select * from D2
 order by CustID, Month desc;
  title1
 'Vertical Joining Using PROC SQL /Interleaved';
 select * from concat_sql_i;
quit;
Out[13]:
SAS Output

Vertical Joining Using PROC SQL /Interleaved

CustID Month purchased_amount
11 Jan 237.4
11 Feb 288.2
12 Jan 249.2
12 Feb 221.7
13 Jan 227.7
13 Feb 274.4
14 Feb 222.9
In [15]:
options nocenter nodate nonumber;
DATA BIRTH;
  INPUT id $ dob : mmddyy.;
  FORMAT dob  mmddyy10.;
  DATALINES; 
03 03/31/1944 
04 08/11/1950
01 01/09/1954 
02 09/12/1959 
05 07/18/1941
;
PROC SORT data=BIRTH; by id; 
title1 'BIRTH File - Listing'; 
PROC PRINT data=BIRTH noobs;  run;
SAS Output

BIRTH File - Listing

id dob
01 01/09/1954
02 09/12/1959
03 03/31/1944
04 08/11/1950
05 07/18/1941
In [16]:
DATA DEATH;
input id $ dod : mmddyy.;
FORMAT dod mmddyy10.;
DATALINES;
07 12/31/2011 
08 02/14/2012
04 12/31/2010 
05 12/12/2012 
06 12/29/2011 
; 
PROC SORT data=DEATH; by id; 
title1 'DEATH File - Listing';
PROC PRINT data=DEATH noobs;  run;
SAS Output

DEATH File - Listing

id dod
04 12/31/2010
05 12/12/2012
06 12/29/2011
07 12/31/2011
08 02/14/2012

Full Join in PROC SQL¶

  • Includes all rows from both tables even if there are no matching rows in either table
  • does not overlay same-name columns.
In [19]:
options nocenter nodate nonumber;
proc sql;
title 'Full Join/PROC SQL does not overlay same-name columns';
select * 
    from BIRTH b full join DEATH d
      on b.id = d.id;
quit;
Out[19]:
SAS Output

Full Join/PROC SQL does not overlay same-name columns

id dob id dod
01 01/09/1954   .
02 09/12/1959   .
03 03/31/1944   .
04 08/11/1950 04 12/31/2010
05 07/18/1941 05 12/12/2012
  . 06 12/29/2011
  . 07 12/31/2011
  . 08 02/14/2012

COALESCE Function¶

  • does overlays columns
  • returns the value of the first nonmissing argument
In [22]:
options nocenter nodate nonumber;
proc sql;
title1 'Full Join/PROC SQL does overlay same-name columns with COALESCE function';
select coalesce(b.ID, d.ID) as ID, dob, dod
    from BIRTH b full join DEATH d
      on b.id = d.id;
quit;
Out[22]:
SAS Output

Full Join/PROC SQL does overlay same-name columns with COALESCE function

ID dob dod
01 01/09/1954 .
02 09/12/1959 .
03 03/31/1944 .
04 08/11/1950 12/31/2010
05 07/18/1941 12/12/2012
06 . 12/29/2011
07 . 12/31/2011
08 . 02/14/2012

Inner Join¶

  • returns only matching rows

COALESCE Function¶

  • does overlays the same-name columns
  • returns the value of the first nonmissing argument
In [20]:
options nocenter nodate nonumber;
proc sql;
title1 'Inner Join/PROC SQL';
select coalesce(b.id, d.id) as id,
       b.dob, d.dod 
   from BIRTH as b
   inner join  DEATH as d
     on b.id = d.id;
quit;
Out[20]:
SAS Output

Inner Join/PROC SQL

id dob dod
04 08/11/1950 12/31/2010
05 07/18/1941 12/12/2012

Inner Join¶

  • specifies the matching criteria in the WHERE clause (as an alternative)
In [21]:
options nocenter nodate nonumber;
proc sql;
title1 'Inner Join 2 /PROC SQL';
select b.id, b.dob, d.dod 
   from BIRTH as b,
        DEATH as d
     where b.id = d.id;
quit;
Out[21]:
SAS Output

Inner Join 2 /PROC SQL

id dob dod
04 08/11/1950 12/31/2010
05 07/18/1941 12/12/2012

Exact match with subquery in PROC SQL¶

In [7]:
options nocenter nodate nonumber;
proc sql;
  select id, dob
  from birth
  where id in (select id from death);
quit;
Out[7]:
SAS Output

DEATH File - Listing

id dob
04 08/11/1950
05 07/18/1941

Left Join¶

  • returns all matching and non-matching rows from the left table
  • returns matching rows from the right table
In [24]:
options nocenter nodate nonumber;
proc sql;
title1 'Left Join/PROC SQL';
select coalesce(b.id, d.id) as id,
       b.dob, d.dod 
   from BIRTH as b
   left join  DEATH as d
     on b.id = d.id;
quit;
Out[24]:
SAS Output

Left Join/PROC SQL

id dob dod
01 01/09/1954 .
02 09/12/1959 .
03 03/31/1944 .
04 08/11/1950 12/31/2010
05 07/18/1941 12/12/2012

Right Join¶

  • returns all matching and non-matching rows from the right table
  • returns matching rows from the left table
In [23]:
options nocenter nodate nonumber;
proc sql;
title1 'Right Join/PROC SQL';
select coalesce(b.id, d.id) as id,
       b.dob, d.dod 
       from BIRTH as b right join DEATH as d
      on b.id = d.id;
quit;
Out[23]:
SAS Output

Right Join/PROC SQL

id dob dod
04 08/11/1950 12/31/2010
05 07/18/1941 12/12/2012
06 . 12/29/2011
07 . 12/31/2011
08 . 02/14/2012

SQL subquery¶

In [28]:
options nocenter nodate nonumber;
*PROC SQL subquery finding BIRTH IDs that are not in the DEATH file; 
proc sql;
title1 'SQL subquery - Finding BIRTH IDs that are not in the DEATH file';
  select id, dob
  from birth
  where id not in(select id from death);
quit;
Out[28]:
SAS Output

SQL subquery - Finding BIRTH IDs that are not in the DEATH file

id dob
01 01/09/1954
02 09/12/1959
03 03/31/1944
In [31]:
options nocenter nodate nonumber;
*PROC SQL subquery finding DEATH IDs that are not in the BIRTH file; ; 
proc sql;
title1 'SQL subquery - Finding DEATH IDs that are not in the BIRTH file';
  select id, dod 
  from death
  where id not in(select id from birth);
quit;
Out[31]:
SAS Output

SQL subquery - Finding DEATH IDs that are not in the BIRTH file

id dod
06 12/29/2011
07 12/31/2011
08 02/14/2012
In [6]:
proc sql;
    select count(*) as N from sashelp.iris;
quit;
SAS Output

The SAS System

N
150
In [5]:
proc sql noprint;
    select count(*) into :nobs_1 separated by ' '
        from sashelp.iris;
quit;
%put &=nobs_1.;
SAS Output

The SAS System

N
150