SAS Support

An Introduction to PROC SQL

Comparing PROC SQL with the SAS DATA Step

From http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001409810.htm (accessed on August 11, 2015))

PROC SQL can perform some of the operations that are provided by the DATA step and the PRINT, SORT, and SUMMARY procedures. The following query displays the total population of all the large countries (countries with population greater than 1 million) on each continent.

proc sql;
   title 'Population of Large Countries Grouped by Continent';
   select Continent, sum(Population) as TotPop format=comma15.
      from sql.countries
      where Population gt 1000000
      group by Continent
      order by TotPop;
quit;

 

 

Sample SQL Output

                 Population of Large Countries Grouped by Continent

                  Continent                                TotPop
                  -----------------------------------------------
                  Oceania                               3,422,548
                  Australia                            18,255,944
                  Central America and Caribbean        65,283,910
                  South America                       316,303,397
                  North America                       384,801,818
                  Africa                              706,611,183
                  Europe                              811,680,062
                  Asia                              3,379,469,458

Here is a SAS program that produces the same result.

title 'Large Countries Grouped by Continent';
proc summary data=sql.countries;
   where Population > 1000000;
   class Continent;
   var Population;
   output out=sumPop sum=TotPop;
run;

proc sort data=SumPop;
   by totPop;
run;

proc print data=SumPop noobs;
   var Continent TotPop;
   format TotPop comma15.;
   where _type_=1;
run;

 

 

Sample DATA Step Output

                        Large Countries Grouped by Continent

                 Continent                                  TotPop

                 Oceania                                 3,422,548
                 Australia                              18,255,944
                 Central America and Caribbean          65,283,910
                 South America                         316,303,397
                 North America                         384,801,818
                 Africa                                706,611,183
                 Europe                                811,680,062
                 Asia                                3,379,469,458

This example shows that PROC SQL can achieve the same results as Base SAS software but often with fewer and shorter statements. The SELECT statement that is shown in this example performs summation, grouping, sorting, and row selection. It also displays the query's results without the PRINT procedure.

PROC SQL executes without using the RUN statement. After you invoke PROC SQL you can submit additional SQL procedure statements without submitting the PROC statement again. Use the QUIT statement to terminate the procedure.

 

Selected Books on SAS

Administrative Healthcare Data: A Guide to Its Origin, Content, and Application Using SAS®  

Craig Dickstein, Renu Gehring

Epub ISBN# 978-1-62959-381-4

Mobi ISBN# 978-1-62959-382-1

PDF ISBN# 978-1-62959-380-7

Hardcopy ISBN# 978-1-61290-886-1

Pages 250

http://www.sas.com/store/prodBK_66981_en.html

 

The Little SAS® Book: A Primer, Fifth Edition

Lora D. Delwiche, Susan J. Slaughter

Epub ISBN# 978-1-61290-400-9

Mobi ISBN# 978-1-61290-945-5

PDF ISBN# 978-1-62959-013-4

Hardcopy ISBN# 978-1-61290-343-9

Pages 376

Learning SAS® by Example: A Programmer's Guide

Ron Cody

Epub ISBN# 978-1-59994-426-5

Mobi ISBN# 978-1-61290-946-2

PDF ISBN# 978-1-62959-014-1

Hardcopy ISBN# 978-1-59994-165-3

Pages 664

 

Step-by-Step Programming with Base SAS® 9.4

Publisher: SAS Institute

Copyright Date: July 2013

The PDF file of this book can be found at:

http://support.sas.com/documentation/cdl/en/basess/64003/HTML/default/viewer.htm#titlepage.htm

Longitudinal Data and SAS®: A Programmer's Guide

Ron Cody

Epub ISBN# 978-1-62959-249-7

Mobi ISBN# 978-1-62959-248-0

PDF ISBN# 978-1-62959-247-3

Hardcopy ISBN# 978-1-58025-924-8

Pages 208

http://www.sas.com/store/books/categories/usage-and-reference/longitudinal-data-and-sas-a-programmer-s-guide/prodBK_58176_en.html

 
Marriott Library Eccles Library Quinney Law Library