Title: SAS PROCs
1SAS PROCs
2PROC Statement Syntax
- PROC name options
- Statements statement options
-
- RUN
3Procedure Options
- DATA
- N Number of subjects is
- Include the number of observations in the output
Proc Print dataMydata.Employee N run
4PROC 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
5Examples
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
6PROC 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.
7Creating 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
8Creating 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
9Creating a View
PROC SQL CREATE VIEW ACustomer AS SELECT FROM
MyData.Customers where Rating'A' Quit Proc
Print DataACustomer Run
10Select 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
11Run 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
12ODBC 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
13Join 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
14Output 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