SAS PROCs

1
SAS PROCs

• ISYS 650

2
PROC Statement Syntax

• PROC name options
• Statements statement options
• RUN

3
Procedure Options

• DATA
• N Number of subjects is
• Include the number of observations in the output

Proc Print dataMydata.Employee N run
4
PROC Statements

• VAR variables
• To use only the variables in the list for
  analysis.
• BY variables
• Cause SAS to repeat the procedure for value of
  the variables, similar to GROUP BY.
• SUM variables
• Include total for specified variables
• TITLE

5
Examples
Proc MEANS dataMydata.Emp VAR Salary BY
Race run
PROC PRINT dataMydata.Emp N VAR Name Sex Race
Salary SUM Salary TITLE 'Employee List' run
PROC SORT dataMydata.Emp BY Name run PROC
PRINT VAR Name Race Salary RUN
6
PROC SQL

• PROC SQL is a powerful Base SAS Procedure that
  combines the functionality of DATA and PROC steps
  into a single step.
• PROC SQL can sort, summarize, subset, join
  (merge), and concatenate datasets, create new
  variables, and print the results or create a new
  table or view all in one step!
• PROC SQL can be used to retrieve, update, and
  report on information from SAS data sets or other
  database products.

7
Creating a SAS Data Set Using ODBC and SQL Create
Table Command
Permanent data set
PROC SQL CONNECT TO ODBC(DSN'MySalesDB2007') CR
EATE TABLE MyData.Customers AS SELECT FROM
CONNECTION TO ODBC(SELECT FROM
Customer) Quit Note End with the Quit
command.
Temporary data set
PROC SQL CONNECT TO ODBC(DSN'MySalesDB2007') CR
EATE TABLE Customers AS SELECT FROM CONNECTION
TO ODBC(SELECT FROM Customer) Quit PROC
PRINT RUN
8
Creating Data Set as a Result of a Join Command
PROC SQL CONNECT TO ODBC(DSN'MySalesDB2007') CR
EATE TABLE temp_sas AS SELECT FROM CONNECTION
TO ODBC(SELECT Customer.CID, Cname, OID, Odate
FROM Customer, Orders where Customer.cidorders.ci
d) Quit Proc Print Run
9
Creating a View
PROC SQL CREATE VIEW ACustomer AS SELECT FROM
MyData.Customers where Rating'A' Quit Proc
Print DataACustomer Run
10
Select Records from a SAS Data Set
PROC SQL SELECT FROM MyData.Customers Where
Rating'A' Quit
PROC SQL SELECT City, Count(CID) AS
NumberOfCustomer FROM MyData.Customers Group By
City Quit
PROC SQL SELECT City, Count(CID) AS
NumberOfCustomer FROM MyData.Customers Group By
City Having Count(CID)gt5 Quit
11
Run SQL DML Command
Delete
PROC SQL DELETE FROM MyData.Emp Where name is
Null Quit
Update
PROC SQL UPDATE MyData.Customers Set
Rating'A' Where CID'C01' Quit
Insert
PROC SQL INSERT INTO MyData.Customers
Values('C49','Smith','SF','C') Quit
12
ODBC Connection to OracleUser and Password
PROC SQL CONNECT TO ODBC(DSN'OracleDChao'
user'dchao' password'dchao') CREATE TABLE
CatSalesPlan AS SELECT FROM CONNECTION TO
ODBC(SELECT FROM SalesPlan) Quit
13
Join a total sales query from a data warehouse
with a management plan in Oracle
PROC SQL CONNECT TO ODBC(DSN'DWNW') CREATE
TABLE CatSales AS SELECT FROM CONNECTION TO
ODBC(SELECT FROM SalesByCatYear where
year1998) Quit PROC SQL CONNECT TO
ODBC(DSN'OracleDChao' user'dchao'
password'dchao') CREATE TABLE CatSalesPlan
AS SELECT FROM CONNECTION TO ODBC(SELECT FROM
SalesPlan) Quit Proc SQL Select C.categoryID,
C.Year,ActualSales, ProjectedSales,
ActualSales/ProjectedSales100 as
PercentSales from CatSales C, CatSalesPlan p
where C.CategoryIDp.CategoryID and
C.Yearp.Year Quit
14
Output Delivery System, ODS

• With ODS you can choose to create output in other
  formats, including HTML, RTF and PDF.

ODS HTML Proc print dataStGpa run ODS HTML
CLose
ODS PDF file'C\MyData\PDFCustomer.PDF' Proc
print dataMydata.Customers run ODS PDF CLose
ODS RTF file'C\MyData\RTFCustomer.RTF' Proc
print dataMydata.Customers run ODS RTF CLose