SAS PROCs ISYS 650 PROC Statement Syntax PROC

SAS PROCs ISYS 650

PROC Statement Syntax PROC name options; Statements statement options; … • RUN;

Procedure Options • DATA = • N = ‘Number of subjects is: ‘ – Include the number of observations in the output Proc Print data=Mydata. Employee N; run;

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

Examples Proc MEANS data=Mydata. Emp; VAR Salary; BY Race; run; PROC PRINT data=Mydata. Emp N; VAR Name Sex Race Salary; SUM Salary; TITLE 'Employee List'; run; PROC SORT data=Mydata. Emp; BY Name; run; PROC PRINT; VAR Name Race Salary; RUN;

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.

Creating a SAS Data Set Using ODBC and SQL Create Table Command Permanent data set: PROC SQL; CONNECT TO ODBC(DSN='My. Sales. DB 2007'); CREATE TABLE My. Data. 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='My. Sales. DB 2007'); CREATE TABLE Customers AS SELECT * FROM CONNECTION TO ODBC(SELECT * FROM Customer); Quit; PROC PRINT; RUN;

Creating Data Set as a Result of a Join Command PROC SQL; CONNECT TO ODBC(DSN='My. Sales. DB 2007'); CREATE TABLE temp_sas AS SELECT * FROM CONNECTION TO ODBC(SELECT Customer. CID, Cname, OID, Odate FROM Customer, Orders where Customer. cid=orders. cid); Quit; Proc Print; Run;

Creating a View PROC SQL; CREATE VIEW ACustomer AS SELECT * FROM My. Data. Customers where Rating='A'; Quit; Proc Print Data=ACustomer; Run;

Select Records from a SAS Data Set PROC SQL; SELECT * FROM My. Data. Customers Where Rating='A'; Quit; PROC SQL; SELECT City, Count(CID) AS Number. Of. Customer FROM My. Data. Customers Group By City Having Count(CID)>5; Quit;

Run SQL DML Command Delete: PROC SQL; DELETE * FROM My. Data. Emp Where name is Null; Quit; Update: PROC SQL; UPDATE My. Data. Customers Set Rating='A' Where CID='C 01'; Quit; Insert: PROC SQL; INSERT INTO My. Data. Customers Values('C 49', 'Smith', 'SF', 'C'); Quit;

ODBC Connection to Oracle: User and Password PROC SQL; CONNECT TO ODBC(DSN='Oracle. DChao' user='dchao' password='dchao'); CREATE TABLE Cat. Sales. Plan AS SELECT * FROM CONNECTION TO ODBC(SELECT * FROM Sales. Plan); Quit;

Join a total sales query from a data warehouse with a management plan in Oracle PROC SQL; CONNECT TO ODBC(DSN='DWNW'); CREATE TABLE Cat. Sales AS SELECT * FROM CONNECTION TO ODBC(SELECT * FROM Sales. By. Cat. Year where year=1998); Quit; PROC SQL; CONNECT TO ODBC(DSN='Oracle. DChao' user='dchao' password='dchao'); CREATE TABLE Cat. Sales. Plan AS SELECT * FROM CONNECTION TO ODBC(SELECT * FROM Sales. Plan); Quit; Proc SQL; Select C. category. ID, C. Year, Actual. Sales, Projected. Sales, Actual. Sales/Projected. Sales*100 as Percent. Sales from Cat. Sales C, Cat. Sales. Plan p where C. Category. ID=p. Category. ID and C. Year=p. Year; Quit;

Output Delivery System, ODS • With ODS you can choose to create output in other formats, including HTML, RTF and PDF. ODS HTML; Proc print data=St. Gpa ; run; ODS HTML CLose; ODS PDF file='C: My. DataPDFCustomer. PDF'; Proc print data=Mydata. Customers ; run; ODS PDF CLose; ODS RTF file='C: My. DataRTFCustomer. RTF'; Proc print data=Mydata. Customers ; run; ODS RTF CLose;