haas mfe sas workshop lecture 1: introduction to sas

Haas MFE SAS WorkshopLecture 1: Introduction to SAS

Peng Liu http://faculty.haas.berkeley.edu/peliu/computing

Haas School of Business, Berkeley, MFE 2006

Haas School of Business, Berkeley, MFE 2006 Peng Liu AND Alexander Vedrashko 2

What is SAS?

Stands for “Statistical Analysis System”, Pronounced “sass”, not spelled out as three

letters. Developed in the early 1970s at North

Carolina State University SAS Institute Inc. was formed in 1976 Now the most widely used statistical software

in both industry and academic circles


Overview of SAS Products

Base SAS - data management and basic procedures

SAS/STAT - statistical analysis SAS/GRAPH - presentation quality graphics SAS/OR - Operations research SAS/ETS - Econometrics and Time Series

Analysis SAS/IML - Interactive Matrix Language SAS/SQL – Structural query language


Getting Started

SAS PRIMER for Financial Engineers: Lecture1: SAS IntroductionLecture2: SAS Data managementLecture3: SAS Financial modelingLecture4: Advanced SAS techniques

Learning by doing, let’s look at real SAS program using actual financial data.


WHY SAS?

Top reasons for financial engineers to use SAS? Reliable Handel large datasets Powerful procedures Quant interviews


A Task

You have obtained a valuable dataset, you want to look at the data, have a sense what those data represent.

Download the data MortgageLoan.xls from computing webpage. Or open from P:\PodiumPC\2006MFE

Often the data is too large that excel cannot contain. We want to use a software to read-in, look-it-up,

summarize, regression, visualize, report… A demonstration - you will do it yourself later.


INPUT DATA

Options nodate nonumber ls=80;

DATA LOAN1;INFILE 'R:\bulk\SAS\MFE\loan.txt' DELIMITER=',';INPUT ID Origination mmddyy10. Term Rate Balance

Appraisal LTV FICO_orig City $ State $2. ;RUN;


Informative messages are written to the SAS log - make sure you read it!

1 DATA LOAN1;2 INFILE 'R:\bulk\SAS\MFE\loan.txt' DELIMITER=',';3 INPUT ID Origination mmddyy10. Term Rate Balance4 Appraisal LTV FICO_orig City $ State $2. ;5 RUN;

NOTE: The infile 'R:\bulk\SAS\MFE\loan.txt' is: File Name=R:\bulk\SAS\MFE\loan.txt, RECFM=V,LRECL=256

NOTE: 100 records were read from the infile 'R:\bulk\SAS\MFE\loan.txt'. The minimum record length was 56. The maximum record length was 69.NOTE: The data set WORK.LOAN1 has 100 observations and 10 variables.NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.01 seconds


DATA STEP

Options nodate nonumber ls=80;


Appraisal LTV FICO_orig City $ State $2. ;RUN;/* DATA STEP */DATA LOAN2; SET LOAN1;LTV1 = 100*(BALANCE/APPRAISAL);LTV2 = ROUND(LTV1,0.1);IF LTV-LTV2 NE 0 THEN ERROR=1; ELSE ERROR=0;RUN;


PROC STEP

Options nodate nonumber ls=80;DATA LOAN1;INFILE 'R:\bulk\SAS\MFE\loan.txt' DELIMITER=',';INPUT ID Origination mmddyy10. Term Rate Balance


PROC PRINT DATA=LOAN2;VAR ID TERM RATE LTV LTV2 ERROR;TITLE 'SAMPLE RECORDS FROM LOAN LEVEL DATA’ ;RUN;


PROC MEANS



PROC PRINT DATA=LOAN2;VAR ID TERM RATE LTV LTV2 ERROR;TITLE 'SAMPLE RECORDS FROM LOAN LEVEL DATA SET';RUN;PROC MEANS DATA=LOAN1; VAR APPRAISAL FICO_ORIG; RUN;


SAS program structure

/* DATA STEP */DATA LOAN2; SET LOAN1;LTV1 = 100*(BALANCE/APPRAISAL);LTV2 = ROUND(LTV1,0.1);IF LTV-LTV2 NE 0 THEN ERROR=1; ELSE ERROR=0;

/* PROCEDURE STEP */PROC MEANS DATA=LOAN1; VAR APPRAISAL FICO_ORIG; RUN;


Output 1

SAMPLE RECORDS FROM LOAN LEVEL DATA

Obs ID Term Rate LTV LTV2 ERROR

1 240248 180 6.375 71.2 71.2 0 2 522967 360 6.625 60.9 60.9 0 3 826556 360 7.125 65.5 65.5 0 4 885563 360 7.000 80.0 80.0 0 5 4243480 360 7.750 79.9 79.9 0

………….

23 5869916 360 7.250 55.4 55.4 0 24 5871655 360 8.250 80.0 80.0 0 25 5875033 360 8.500 80.0 80.0 0 26 5889419 360 8.250 57.9 57.9 0 27 5903222 360 7.750 48.4 48.4 0 28 5909103 360 7.750 75.2 75.2 0 29 5934371 360 7.625 50.0 50.0 0 30 5943458 360 7.625 80.0 80.0 0


Output 2

SAMPLE RECORDS FROM LOAN LEVEL DATA

The MEANS Procedure

Variable N Mean Std Dev Minimum Maximum

Appraisal 100 691833.28 364426.29 220000.00 2351000.00

FICO_orig 100 731.5700000 52.9597245 564.0000000 805.0000000


What you need to run SAS

SAS software for Windows/Unix/Mac OS Available through Haas on Bear and on the

Terminal Server One year licenses are available to UC Berkeley

students for $30 http://softdist.berkeley.edu/ Current version for windows 9.1.3

SAS dataset(s) Can be created from external files in many formats,

can be directly input in data step. SAS program(s)

Data step(s), Proc step(s), or both


SAS Datasets

Structure: Rows-obs.; Columns-var. Dataset names must be 32 characters or less,

constructed of letters, digits and the underscore Good idea not to start names with an underscore,

special system variables are named that way. Missing values are handled consistently in

SAS, and are represented by a period (.) A SAS dataset contains not only data bu also

the information on varialbe type, label, etc. Temporary v.s. Permanent dataset


SAS programs-Basic Structure 1

SAS is composed of a series of SAS statement. Each SAS statement must end in a semicolon (;)

All SAS programs for windows must end with statement: RUN; There are two main components in most SAS programs – (the

SAS programmer is always doing one of the two things) The DATA steps and the PROCedure steps. You can combine as many data and proc steps in whatever order

you want. Data steps begin with the word data and procedure steps begin with

the word proc. The data steps allow you to read raw/sas/other data, manipulate data

by concatenating, merging and subsetting data. The data step is used to prepare your data for use by one or more SAS procedures

The procedure steps perform analysis on the data, and produce statistical and graphical output.


SAS programs-Basic Structure 2

Add comments using asterisk * …; or /* …*/ The RUN; command signals to SAS that the previous

commands can be executed. There are global options (like linesize and pagesize)

as well as options specific to datasets and procedures. Variable names must be 32 characters or less,

constructed of letters, digits and the underscore character. There are virtually no reserved keywords in SAS; it's very good at figuring things out by context.

SAS is not case sensitive, except inside of quotation mark ‘ ’ or “ ” .

The placement of the SAS statements does not effect SAS processing. Create your own programming style!


Running PC SAS

Open SAS program (clicking SAS icon) Load or type your SAS program in the

Program Editor window(Enhanced Editor) Run/Submit: F8=RUN; Check log for error (F6=Log) Check output/Graphic windows for

results/graph(F7=output) Revise code (F5 PGM) CTRL+E = Clear screen


Importing/Exporting data

PROC IMPORT OUT= WORK.loan DATAFILE ="R:\bulk\SAS\MFE\MortgageLoan.xls"

DBMS=EXCEL REPLACE;SHEET="Loan$"; GETNAMES=YES;

RUN;

PROC EXPORT DATA= WORK.Loan2OUTFILE ="R:\bulk\SAS\MFE\loan2.xls"

DBMS=EXCEL REPLACE;SHEET="loan";

RUN;


Create a permanent dataset

Two-level name: libref.dsname Syntax: LIBNAME LIBREF ‘PATH’;

LIBREF is the library reference name you provided. PATH is the physical directory on your computer.

Example-creating a permanent SAS dataLIBNAME MFE 'R:\bulk\SAS\MFE\';DATA MFE.LOAN; SET LOAN; RUN;

Example-reading an external SAS datasetDATA LOAN; SET MFE.LOAN;

LTV1 = 100*(BALANCE/APPRAISAL);RUN;


Read Raw Data 1

Fixed Format1234567890123456789012345

024024807/19/20021806.375052296708/14/20023606.625082655602/18/20023607.125088556305/06/20023607424348007/10/20013607.75429657109/11/20013607.125

Data loan;Infile 'R:\bulk\SAS\MFE\loan.txt';Input id 1-7 month 8-9 day 11-12

year 14-17 city $ 32-42 state $50-51;

Run;

/*using fileref*/Filename xxx 'R:\bulk\SAS\MFE\

loan.txt';Data loan;Infile xxx;Input id 1-7 month 8-9 day 11-12

year 14-17 city $ 32-42 state $50-51;

Run;


Read Raw Data 2

List Format 0240248 07/19/2002 180 6.3750522967 08/14/2002 360 6.6250826556 02/18/2002 360 7.1250885563 05/06/2002 360 74243480 07/10/2001 360 7.754296571 09/11/2001 . 7.125

Filename xxx 'R:\bulk\SAS\MFE\loan.txt';

Data loan;Infile xxx;Input id origination mmddyy10. Term

Rate Balance Appraisal LTV

FICO_orig city & $ State $2. ;Run;

/* note:1.May need to specify length for

cha. Var.to avoid default truncation at length=8.

2.Make sure mising/blank field is replaced by period.

*/


Read Raw Data 3

Delimited Format0240248,07/19/2002,180,6.375

0522967,08/14/2002,360,6.625

0826556,02/18/2002,360,7.125

0885563,05/06/2002,360,7

4243480,07/10/2001,360,7.75

4296571,09/11/2001,360,7.125

filename xxx 'R:\bulk\SAS\MFE\loan.txt' ;

DATA LOAN1;

INFILE xxx DELIMITER=',';

INPUT ID Origination mmddyy10. Term Rate Balance Appraisal LTV FICO_orig City $ State $2. ;

RUN;


Modifiers for List Input

The colon (:) modifier for list input tells SAS to use a format for input, but to stop when the next whitespace is found.

Data like:17,244 2,500,300 600 12,003 14,120 2,300 4,232 25could be read using an input statement likeinput x1 : comma. x2 : comma. x3 : comma. x4 : comma. ;

The ampersand (&) modifier tells SAS to use two whitespace characters to signal the end of a character variable, allowing embedded blanks to be read using list input. Thus, the statements:

input city & $ state $ 2.; could be used to read data such as

SAN JOSE,CABATON ROUG,LAUPPER SADD,NJ


Other Modifiers for the Input Statement

+ number advance number columns. # number advance to line number. / advance to next line. trailing @ hold the line to allow further input

statements in this iteration of the data step on the same data.

trailing @@ hold the line to allow continued reading from the line on subsequent iterations of the data step.


Variable Lists

SAS provides several different types of variable lists, which can be used in all procedures, and in some data step statements.

Numbered List - When a set of variables have the same prefix, and the rest of the name is a consecutive set of numbers, you can use a single dash (-) to refer to an entire range:

x1 - x3 for x1, x2, x3; x01 - x03 for x01, x02, x03 Colon list - When a set of variables all begin with the same

sequence of characters you can place a colon after the sequence to include them all. If variables a, b, xheight, and xwidth have been defined, then x: for xwidth, xheight.

Special Lists - Three keywords refer to a list with the obvious meaning:

_numeric_ , _character_, _all_ In a data step, special lists will only refer to variables which were already defined when the list is encountered.


Variable Lists (cont'd)

Name range list - When you refer to a list of variables in the order in which they were defined in the SAS data set, you can use a double dash (--) to refer to the range:

If the input statementINPUT ID Origination mmddyy10. Term Rate Balance Appraisal

LTV FICO_orig City $ State $2. ;was used to create a data set, then LTV -- state refers to LTV FICO_orig city state If you only want character or numeric variables in the name

range, insert the appropriate keyword between the dashes: id _character_ state refers to city and state In general, variables are defined in the order they appear in the

data step. If you're not sure about the order, you can check using proc contents.


Display SAS Dataset

Proc content data=loan; run; The short option limits the output to just a list of variable names. The position option orders the variables by their position in the data

set, instead of the default alphabetical order. This can be useful when working with double dashed lists.

The directory option provides a list of all the data sets in the library that the specified data set comes from, along with the usual output for the specified data set.

proc print DATA = loan2 (firstobs=60 obs=100);VAR id origination rate ltv fico_orig city;run; Useful statement in proc print:

ID state; SUM error;


SAS Functions: Mathematical


SAS Functions: Statistical


SAS Functions: other numerical

Round(arg, amount) Int(arg) Ceil(arg) Floor(arg) Rannor(seed) Ranuni(seed) Ranbin(seed,n,p) Runtbl(seed,p1,p2,… pn)


Operators in SAS

Arithmetic operators:* Multiplication + addition / division- subtraction ** exponentiation

Comparison Operators:= or eq equal to ^= or ne not equal to> or gt greater than >= or ge greater than or equal to< or lt less than <= or le less than or equal to

Boolean Operators:& or and and | or or or ^ or not negation

Other Operators:>< minimum; <> maximum; || char. concatenation

The in operator lets you test for equality to any of several constant values. x in (1,2,3) is the same as x=1 | x=2 | x=3.


Using Statistical Summary Functions

You can use variable lists in all the statistical summary functionsby preceding the list with the word “of”; for example:

xm = mean(of x1-x10);vmean = mean(of thisvar -- thatvar);

Without the of, the single dash is interpreted in its usual way, that is as a minus sign or the unary minus operator; thus

xm = mean(of x1-x10);is the same as

xm = mean(x1,x2,x3,x4,x5,x6,x7,x8,x9,x10);but

xm1 = mean(x1-x10);calculates the mean of x1 minus x10, and

xm2 = mean(x1--x10);calculates the mean of x1 plus x10.


SAS Functions: Character 1 compress(target,<chars-to-remove>)

expr = "one, two: three:";new = compress(expr,",:");

results in new equal to one two threeWith no second argument compress removes blanks. index(source,string) - finds position of string in source

where = "university of california";i = index(where,"cal");

results in i equal to 15 indexc(source,string) - finds position of any character in string in source

where = "berkeley, ca";i = indexc(where,"abc");

results in i equal to 1, since b is in position 1.index and indexc return 0 if there is no match. left(string) - returns a left-justied character variable length(string) - returns number of characters in a string

length returns 1 if string is missing, 12 if string is uninitialized repeat(string,n) - repeats a character value n times reverse(string) - reverses the characters in a character variable


SAS Functions: Character 2 right(string) - returns a right-justied character variable both = right(one) || left(two);

insures no blanks between the two concatenated variables. trim(string) - returns string with leading blanks removed upcase(string) - converts lowercase to uppercase

lowcase(string)- converts uppercase to lowercase scan(string,n,<delims>) - returns the nth word" in string

field = "smith, joe";first = scan(field,2," ,");

results in first equal to joeIf you omit delims, SAS uses most non-alphanumeric characters

substr(string,position,<n>) - returns pieces of a variablefield = "smith, joe";

last = substr(field,1,index(field,",") - 1);results in last equal to smith translate(string,to,from) - changes from chars to to chars

word = "eXceLLent";new = translate(word,"xl","XL");

results in new equal to excellent verify(source,string) - return position of first char. in source which is not in string

check = verify(val,"0123456789."); results in check equal to 0 if val is a character string containing only numbers and periods .


Data Type Conversion-Problem

SAS only has three data types: Numeric, Character and Date/time.

When you accidentally mix variable types, SAS tries to fix your program by converting them.

Log File - “Note: Numeric Values have been converted to Character!” Cannot ignore this!

For example: 110 can be numeric or character, when you use numerical function on character variables or vice versa. SAS tries to convert to appropriate data type first, then perform function calculations.

How to Fix? A practical way is to use INPUT/PUT functions. Close cousin of input/put statements, but different!


Data Type Conversion-FIX

Character to Numeric

new=INPUT (old, informat);

Informat must be the type you are converting to – numeric

Rate_num=input (rate_char,5.2);

Demo- loan5

Numeric to Character

new=PUT (old, format);

Informat must be the type you are converting from – numeric

Rate_char=put (rate, 5.2);


SAS Date and Time - values

There are three types of date and time values which SAS can handle, shown with their internal representation in parentheses:1. Time values (number of seconds since midnight)2. Date values (number of days since January 1, 1970)3. Datetime values (number of seconds since January 1, 1970)

You can specify a date and/or time as a constant in a SAS program by surrounding the value in quotes, and following it with a t, a d or a dt. The following examples show the correct format to use:

3PM => '3:00p't or '15:00't or '15:00:00'tJanuary 4, 1937 => '4jan37'd

9:15AM November 3, 1995 =>'3nov95:9:15'dt or '3nov95:9:15:00'dt


SAS Date and Time - Formats

SAS provides three basic informats for reading dates which are recorded as all numbers, with or without separators:1. ddmmyyw. - day, month, year (021102, 6-3-2002, 4/7/2000)2. mmddyyw. - month, day, year (110202, 3-6-2002, 7/4/2000)3. yymmddw. - year, month, day (021102, 2002-3-6, 2000/7/4)

These informats will correctly recognize any of the followingseparators: blank : - . /, as well as no separator.

For output, the ddmmyyXw., mmddyyXw. and yymmddXw. formats are available, example using format statement

where “X" species the separator as follows:

B - blank C - colon(:) D - dash(-)N - no separator P - period(.) S - slash(/)


SAS Date and Time - Functions

datepart - Extract date from datetime valuedateonly = datepart(present);

day,month year - Extract part of a date valueday = day(date);

dhms - Construct value from date, hour, minute and secondpresent_time=dhms(dateonly,10,00,00);

mdy - Construct date from month, day and yeardate = mdy(mon,day,1996);

time - Returns the current time of daynow = time();

today - Returns the current datedatenow = today();

intck - Returns the number of intervals between two valuesdays=intck('day', origination, today());

intnx - Increments a value by a number of intervalsexpiration=intnx('month', origination, term); format expiration yymmdds10.;


Titles and Footnotes

SAS allows up to ten lines of text at the top (titles) and bottom(footnotes) of each page of output, specified with title andfootnote statements. The form of these statements is

title<n> text; or footnote<n> text;where n, if specified, can range from 1 to 10, and text must besurrounded by double or single quotes.

If text is omitted, the title or footnote is deleted; otherwise it remains in effect until it is redefined. Thus, to have no titles, use:

title; By default SAS includes the date and page number on the top of

each piece of output. These can be suppressed with the nodate and nopage system options.


System Options

Syntax option opt; Check out all options: proc options; Frequently used options:

Display: Date/nodate Number/nonumber Center/nocenter Linesize=64-256 eg: ls=80; Pagesize=20-200 eg: ps=100; Label/nolabel


Dataset Options

Syntax: DATA dsname (opt); Example:

data loan_CA (where=(state='CA') drop=id);set loan;run;

Frequently used options (Drop|keep=var-list) (Rename=(old=new…)) (Where=(logical expressions)) (label=‘dataset description’) (Obs/firstobs=n) (used only with set or data option of a proc)


Resources: Introductory Books

http://www.sas.com/apps/pubscat/welcome.jsp The Little SAS Book: a primer (3rd) is popular for beginners Mastering the SAS System, 2nd Edition, by Jay A. Jae, Van Nostrand

Reinhold Quick Start to Data Analysis with SAS, by Frank C. DiIorio and Kenneth

A. Hardy, Duxbury Press. How SAS works: a comprehensive introduction to the SAS System, by

P.A. Herzberg, Springer-Verlag Applied statistics and the SAS programming language, by R.P. Cody,

North-Holland, New York SAS Institute produces several introductory guides; most are at a very

elementary level. Among them are: Introducing the SAS System, Version 6, First Edition SAS Language and Procedures: Introduction, Version 6, First Edition SAS Introductory Guide for Personal Computers, Release 6.03 SAS Applications Programming: A Gentle Introduction


Online Resources (sample)

Online help Sample Programs, distributed with SAS on all platforms.

SAS Institute Home Page: http://www.sas.com SAS Institute Technical Support:

http://www.sas.com/service/techsup/find answer.html

Others: http://www.math.yorku.ca/SCS/StatResource.html#SAS http://www.indiana.edu/~statmath/stat/sas/win/ http://www.ats.ucla.edu/stat/sas/ http://statcomp.ats.ucla.edu/sas/

haas mfe sas workshop lecture 1: introduction to sas

Documents

sas data managementlecture3

data statement

actual financial data

data set work

runproc print data

data loan12 infile r

data loan2 set loan1ltv1

sas introductionlecture2