1 / 14

SAS PROCs

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;

oria
Download Presentation

SAS PROCs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 data=Mydata.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 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;

  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'); CREATE 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'); CREATE 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'); 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;

  9. Creating a View PROC SQL; CREATE VIEW ACustomer AS SELECT * FROM MyData.Customers where Rating='A'; Quit; Proc Print Data=ACustomer; 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)>5; 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 Oracle:User 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 year=1998); 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/ProjectedSales*100 as PercentSales from CatSales C, CatSalesPlan p where C.CategoryID=p.CategoryID and C.Year=p.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 data=StGpa ; run; ODS HTML CLose; ODS PDF file='C:\MyData\PDFCustomer.PDF'; Proc print data=Mydata.Customers ; run; ODS PDF CLose; ODS RTF file='C:\MyData\RTFCustomer.RTF'; Proc print data=Mydata.Customers ; run; ODS RTF CLose;

More Related