The Information Delivery Process Data In Manage Organize

The Information Delivery Process Data In Manage Organize Exploit Information Out

Turning Data Into Information Data DATA Step Data SAS PROC Data Sets Steps PROC Steps Information 2

Turning Data Into Information Process of delivering meaningful information: 80% Data-related: • Access • Scrub • Transform • Manage • Store and retrieve 20% Analysis 3

The Raw Data Partial fixed-column raw data file: 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 4

Browsing the Data Values Listing of Flight Crew Employees Obs empid lastname firstname jobcode salary 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 GOLDENBERG WILLIAMS PERRY MCGWIER-WATTS SCOTT THACKER GRAHAM DREWRY HORTON DOWN CHERVENY BELL GLENN HOLMAN NOE DESIREE ARLENE M. ROBERT A. CHRISTINA HARVEY F. DAVID S. DEBORAH S. SUSAN THOMAS L. EDWARD BRENDA B. THOMAS B. MARTHA S. GREGORY A. BARBARA E. PILOT 1 FLTAT 1 PILOT 3 FLTAT 2 FLTAT 1 FLTAT 2 PILOT 1 FLTAT 1 PILOT 1 FLTAT 2 PILOT 1 PILOT 3 PILOT 2 FLTAT 2 50221. 62 23666. 12 21957. 71 96387. 39 32278. 40 24161. 14 32024. 93 55377. 00 23705. 12 56584. 87 38563. 45 59803. 16 120202. 38 93001. 09 37101. 32 0031 0040 0071 0082 0091 0106 0275 0286 0309 0334 0347 0355 0366 0385 0390 5

Reading a Raw Data File SAS Data Set 6

Reading Raw Data Files Raw Data File 0031 GOLDENBERG DESIREE 0040 WILLIAMS ARLENE M. 0071 PERRY ROBERT A. 0082 MCGWIER-WATTSCHRISTINA SAS Data Set DATA Step data. . . ; infile. . . ; input. . . ; run; empid lastname firstname 0031 GOLDENBERG DESIREE 0040 WILLIAMS ARLENE M. 0071 PERRY ROBERT A. 0082 MCGWIER-WATTS CHRISTINA 7

Reading Raw Data Files In order to create a SAS data set from a raw data file, you must • start a DATA step and name the SAS data set being created (DATA statement) • identify the location of the raw data file to read (INFILE statement) • describe how to read the data fields from the raw data file (INPUT statement). 8

Creating a SAS Data Set with the DATA Statement General form of the DATA statement: DATA SAS-data-set(s); This DATA statement creates a SAS data set called WORK. EMPDATA: data work. empdata; 9

Pointing to a Raw Data File with the INFILE Statement General form of the INFILE statement: INFILE‘filename’ ; Examples: OS/390 infile ‘edc. prog 1. employee’; UNIX infile ‘/user/prog 1/employee. dat’; WIN infile ‘C: workshopwinsas prog 1employee. dat’; 10

Reading Raw Data Using Column Input General form of column input: INPUTvariable $ startcol-endcol …; To read raw data values with column input, 1. name the SAS variable you want to create 2. use a dollar sign, $, if the SAS variable is character 3. specify the starting column, a dash, and the ending column of the raw data field. 11

Reading Raw Data Using Column Input 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 0031 GOLDENBERG input empid DESIREE lastname $ 1 -4 $ PILOT 1 50221. 62 5 -17 12

Reading Raw Data Using Column Input 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 0031 GOLDENBERG input empid DESIREE lastname $ 1 -4 $ PILOT 1 50221. 62 5 -17 firstname $ 18 -30 13

Reading Raw Data Using Column Input 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 0031 GOLDENBERG input empid DESIREE lastname $ 1 -4 $ PILOT 1 50221. 62 5 -17 firstname $ 18 -30 jobcode $ 31 -36 14

Reading Raw Data Using Column Input 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 0031 GOLDENBERG input empid DESIREE lastname $ 1 -4 $ PILOT 1 50221. 62 5 -17 firstname $ 18 -30 jobcode salary $ 31 -36 37 -45; 15

Reading Raw Data Using Column Input 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 0031 GOLDENBERG input empid DESIREE lastname $ 1 -4 $ PILOT 1 50221. 62 5 -17 firstname $ 18 -30 jobcode salary $ 31 -36 37 -45; 16

Business Scenario International Airlines is preparing to review its flight crew. The immediate goal is to read the Excel spreadsheet and create a SAS data set. Excel Spreadsheet SAS Data Set 17

What is the Import Wizard? A point-and-click graphical interface that enables you to create a SAS data set from several types of external files including • d. BASE file (*. DBF) • Excel 97 Spreadsheet (*. XLS) • Microsoft Access Table • Delimited file (*. *) • Comma Separated Values (*. CSV) 18

The Raw Data The aircraft data is stored in a fixed-column raw data file: Partial data: aircraft ID last maintenance date 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 Jet. Cruise LF 5200 030005 02/15/1999 07/05/2001 Jet. Cruise LF 5200 030008 03/06/1996 04/02/2002 Jet. Cruise LF 5200 030009 10/14/1998 09/15/2001 Jet. Cruise LF 5200 030011 09/04/1998 08/31/2001 Jet. Cruise LF 5200 030012 01/02/1994 03/29/2001 Jet. Cruise LF 5200 030013 02/01/1996 11/23/2002 Jet. Cruise LF 5200 030015 06/24/1998 02/06/2001 aircraft model date in service 19

Using Formatted Input Raw Data File Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 Jet. Cruise LF 5200 030005 02/15/1999 07/05/2001 Jet. Cruise LF 5200 030008 03/06/1996 04/02/2002 DATA Step data sas-data-set-name; infile raw-filename; input pointer-control variable informat-name; run; The raw data file will be read with formatted input. SAS Data Set 20

What is a SAS Format? A format is an instruction that the SAS System uses to write data values. SAS formats have the following form: <$>format. 21

SAS Formats Selected SAS formats: w. d standard numeric format $w. standard character format COMMAw. d commas in a number: 12, 234. 21 DOLLARw. d dollar signs and commas in a number: $12, 234. 41 22

SAS Formats 23

Using Formatted Input General form of the INPUT statement with formatted input: INPUTpointer-control column informat. . . ; Pointer control: @n moves the pointer to column n. +n moves the pointer n positions. 24

Using Formatted Input Formatted input can be used to read nonstandard data values by • moving the input pointer to the starting position of the field • specifying a column name • specifying an informat. An informat specifies the width of the input field and how to read the data values that are stored in the field. 25

Using Formatted Input General form of an informat: $informat-namew. d $ indicates a character format. informat-names the informat. w is an optional field width. . is the required delimiter. d optionally, specifies a decimal for numeric informats. 26

Selected Informats 7. or 7. 0 reads seven columns of numeric data. 7. 2 reads seven columns of numeric data and inserts a decimal point in the data value. $5. reads five columns of character data and removes leading blanks. $CHAR 5. reads five columns of character data and preserves leading blanks. 27

Selected Informats COMMA 7. reads seven columns of numeric data and removes selected nonnumeric characters, such as dollar signs and commas. PD 4. reads four columns of packed decimal data. MMDDYY 10. reads dates of the form 01/20/2000. 28

Working with Date Values The raw data file contains date values. These date values will be read with the MMDDYY 10. informat: 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jetcruise LF 5200 030003 04/05/1990 3/11/2001 Jetcruise LF 5200 030005 02/15/1990 7/05/2001 Jetcruise LF 5200 030008 03/06/1990 4/02/2002 29

Converting Dates to SAS Date Values SAS uses date informats to read and convert dates to SAS date values. For example, Stored Value Informat 10/29/1999 MMDDYY 10. 29 OCT 1999 DATE 9. Converted Value 14546 29/10/1999 DDMMYY 10. 14546 30

SAS Formats Selected SAS date formats: MMDDYYw. 101692 (MMDDYY 6. ) 10/16/92 (MMDDYY 8. ) 10/16/1992 (MMDDYY 10. ) DATEw. 16 OCT 92 (DATE 7. ) 16 OCT 1992 (DATE 9. ) 31

Locating and Browsing the Raw Data File Browse the raw data file and determine the column layout and type: Partial raw data file: aircraft ID last maintenance date 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 Jet. Cruise LF 5200 030005 02/15/1999 07/05/2001 Jet. Cruise LF 5200 030008 03/06/1996 04/02/2002 Jet. Cruise LF 5200 030009 10/14/1998 09/15/2001 Jet. Cruise LF 5200 030011 09/04/1998 08/31/2001 Jet. Cruise LF 5200 030012 01/02/1994 03/29/2001 Jet. Cruise LF 5200 030013 02/01/1996 11/23/2002 Jet. Cruise LF 5200 030015 06/24/1998 02/06/2001 aircraft model date in service 32

Starting the DATA Step Use the DATA statement to begin the DATA step and name the SAS data set: data work. aircraft; other SAS statements run; Use the INFILE statement to identify the input raw data file: data work. aircraft; infile ‘aircraft. dat’; other SAS statements run; 33

Writing the INPUT Statement Use the INPUT statement and pointer control to read the record starting with the first column. Read the value with the $16. informat and assign it to the variable MODEL. 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 data work. aircraft; infile ‘aircraft. dat’; input @1 model $16. other SAS statements run; 34

Writing the INPUT Statement Use the INPUT statement and pointer control to read the record starting with column 18. Read the value with the $6. informat and assign the value to AIRCRAFTID. 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 data work. aircraft; infile ‘aircraft. dat’; input @1 model $16. @18 aircraftid $6. other SAS statements run; 35

Writing the INPUT Statement Use the INPUT statement and pointer control to read the record starting with column 25. Read the value with the MMDDYY 10. informat and assign the value to INSERVICE. 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 data work. aircraft infile ‘aircraft. dat’; input @1 model $16. @18 aircraftid $6. @25 inservice mmddyy 10. other SAS statements run; 36

Writing the INPUT Statement Use the INPUT statement and pointer control to read the record starting with column 36. Read the value with the MMDDYY 10. informat and assign the value to LASTMAINT. 1 1 2 2 3 3 4 4 1 ---5 ----0 ----5 ----0 ----5 Jet. Cruise LF 5200 030003 04/05/1994 03/11/2001 data work. aircraft; infile ‘aircraft. dat’; input @1 model $16. @18 aircraftid $6. @25 inservice mmddyy 10. @36 lastmaint mmddyy 10. ; run; 37

SAS Syntax Rules SAS statements are free-format. • They can begin and end in any column. • One or more blanks or special characters can be used to separate words. • A single statement can span multiple lines. • Several statements can be on the same line. Unconventional spacing data work. mech_pilot; infile 'c: coursedataemplist. dat'; input lastname $ 1 -20 firstname $ 21 -30 jobtitle $ 36 -43 salary 54 -59; run; proc means data=work. mech_pilot n mean; class jobtitle; var salary; run; 38

SAS Syntax Rules SAS statements are free-format. • They can begin and end in any column. • One or more blanks or special characters can be used to separate words. • A single statement can span multiple lines. • Several statements can be on the same line. Unconventional spacing data work. mech_pilot; infile 'c: coursedataemplist. dat'; input lastname $ 1 -20 firstname $ 21 -30 jobtitle $ 36 -43 salary 54 -59; run; proc means data=work. mech_pilot n mean; class jobtitle; var salary; run; 39

SAS Syntax Rules SAS statements • usually begin with an identifying keyword • always end with a semicolon. data work. mech_pilot; infile 'c: coursedataemplist. dat'; input lastname $ 1 -20 firstname $ 21 -30 jobtitle $ 36 -43 salary 54 -59; run; proc print data=work. mech_pilot; run; proc means data=work. mech_pilot n mean; class jobtitle; var salary; run; 40

Adding a New Variable Create a new variable by extracting the four-digit year values from the SAS date values. Aircraft Service Records 41

Using an Assignment Statement An assignment statement evaluates an expression and assigns the resulting value to a variable. General syntax of an assignment statement: variable=expression; 42

Using Operators Selected operators for basic arithmetic calculations in an assignment statement: 43

Using SAS Functions A SAS function is a routine that returns a value that is determined from specified arguments. General syntax of a SAS function: function-name(argument 1, argument 2, . . . ) 44

Using SAS Functions SAS functions · perform arithmetic operations · compute statistics (for example, mean) · manipulate SAS dates and process character values · perform many other tasks. 45

Creating a Vertical Bar Chart Use the GCHART procedure and the VBAR statement to create a vertical bar chart. proc gchart data=work. aircraft; vbar yrbeg_service; title 'Aircraft In Service, by Year'; run; 46

Reading a Subset of Raw Data Use the DATA step that was written earlier. Add a subsetting IF statement to process only the subset in which the value of AGE is at least 15. data work. aircraft; infile ‘aircraft. dat’; input @1 model $16. @18 aircraftid $6. @25 inservice mmddyy 10. @36 lastmaint mmddyy 10. ; yrbeg_service=year(inservice); age=year(today())-yrbeg_service; if age>=15; run; 47

What Is a SAS Data Library? 48

What Is a SAS Data Library? Regardless of which host operating system you use, you identify SAS data libraries by assigning each one a libref 49

What Is a SAS Data Library? By default, SAS creates two SAS data libraries: • a temporary library called WORK • a permanent library called SASUSER. WORK SASUSER 50

SAS Data Libraries You can think of a SAS data library as a drawer in a filing cabinet and a SAS data set as one of the file folders in the drawer. FILES LIBRARIES 51

SAS Data Libraries When you invoke SAS, you automatically have access to a temporary and a permanent SAS data library. • WORK - temporary library WORK • SASUSER - permanent library SASUSER You can create and access your own permanent libraries. IA • IA - permanent library 52

Reading a SAS Data Set Input data set Output data set SET statement DATA statement Temporary SAS data set Permanent SAS data set 53

Two-level SAS Filenames Every SAS file has a two-level name. libref. filename • The first name (libref) refers to the library. WORK The data set MECH_PILOT is a SAS file in the WORK library. • The second name (filename) refers to the file in the library. SASUSER mech_pilot IA 54

Browsing the Data Portion The PRINT procedure displays the data portion of a SAS data set. By default, PROC PRINT displays • all observations • all variables • OBS column on the left-hand side. 55

Browsing the Data Portion General form of the PRINT procedure: PROC PRINT DATA=SAS-data-set; RUN; Example: proc print data=work. empdata; run; 56

Objectives • Generate list reports using the PRINT procedure. • Display selected variables in a list report using the VAR statement. • Display selected observations in a list report using the WHERE statement. • Sort the observations in a SAS data set using the SORT procedure. 57

Creating a List Report PROC Step proc print data=work. empdata; var empid salary jobcode; run; The SAS System Obs 1 2 3 empid 0031 0040 0071 salary 50221. 62 23666. 12 21957. 71 jobcode PILOT 1 FLTAT 1 58

Formatting Data Values proc print data=work. empsort; format salary dollar 11. 2; run; The SAS System Obs 1 2 3 empid 0525 3370 2291 lastname BEAUMONT BEDNAREK BEECH firstname SALLY T. AMY L. DAVID C. jobcode salary PILOT 3 $112, 783. 21 PILOT 3 $122, 933. 19 PILOT 2 $73, 168. 13 59

Creating a Frequency Report The FREQ Procedure Job Code Cumulative jobcode Frequency Percent ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ FLTAT 1 41 14. 70 FLTAT 2 70 25. 09 111 39. 78 FLTAT 3 88 31. 54 199 71. 33 PILOT 1 46 16. 49 245 87. 81 PILOT 2 18 6. 45 263 94. 27 PILOT 3 16 5. 73 279 100. 00 PROC Step 60

Creating a Frequency Report The FREQ procedure displays frequency counts of the data values in a SAS data set. General form of a simple PROC FREQ step: PROC FREQDATA=SAS-data-set; RUN; Example: proc freq data=work. empsort; run; 61

Creating a One-Way Frequency Report Only variables listed on the TABLES statement are included in the frequency counts. These are typically variables that have a limited number of distinct values. General form of a PROC FREQ step: PROC FREQDATA=SAS-data-set; TABLES SAS-variables; RUN; 62

Calculating Job Code Frequencies Job Code Frequency Report The FREQ Procedure Job Code Cumulative Job_Code Frequency Percent -----------------------------------Flight Attendant 199 71. 33 Pilot 80 28. 67 279 100. 00 63

Calculating Salary Frequencies Salary Frequency Report The FREQ Procedure Annual Salary Cumulative Salary Frequency Percent ----------------------------------Low to $25, 000 41 14. 70 $25, 000 to $50, 000 172 61. 65 213 76. 34 $50, 000 and up 66 23. 66 279 100. 00 64

Calculating Job Code/Salary Frequencies The FREQ Procedure Table of Job_Code by Salary Job_Code(Job Code) Frequency Percent Row Pct Col Pct Salary(Annual Salary) Low to $ $25, 000 |$50, 000 | 25, 000 to $50, 0|and up | 00 | | ----------------------| Flight Attendant 41 158 | 0 | 14. 70 56. 63 | 0. 00 | 20. 60 79. 40 | 0. 00 | 100. 00 91. 86 | 0. 00 | ----------------------| Pilot 0 14 | 66 | 0. 00 5. 02 | 23. 66 | 0. 00 17. 50 | 82. 50 | 0. 00 8. 14 | 100. 00 | ----------------------Total 41 172 66 Total 199 71. 33 80 28. 67 279 65

Creating a Frequency Report By default, PROC FREQ • analyzes every variable in the SAS data set • displays each distinct data value • calculates the number of observations in which each data value appears (and corresponding percentage) • indicates for each variable how many observations have missing values. 66

Calculating Summary Statistics The MEANS procedure displays simple descriptive statistics for the numeric variables in a SAS data set. General form of a simple PROC MEANS step: PROC MEANSDATA=SAS-data-set; RUN ; Example: proc means data=ia. aircraftcap; run; 67

Calculating Summary Statistics proc means data=ia. aircraftcap; run; The SAS System The MEANS Procedure Variable N Mean Std Dev Minimum Maximum ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ-ƒƒƒƒƒƒƒ inservice 64 10647. 97 1966. 95 3282. 00 13125. 00 totpasscap 64 163. 4687500 47. 2208485 97. 0000000 290. 0000000 ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 68

Calculating Summary Statistics By default, PROC MEANS • analyzes every numeric variable in the SAS data set • prints the statistics N, MEAN, STD, MIN, and MAX • excludes missing values before calculating statistics. 69

Selecting Variables proc means data=ia. aircraftcap; var totpasscap; run; The SAS System The MEANS Procedure Analysis Variable : totpasscap N Mean Std Dev Minimum Maximum ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 64 163. 4687500 47. 2208485 97. 0000000 290. 0000000 ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 70

Grouping Observations proc means data=ia. aircraftcap maxdec=2; var totpasscap; class model; run; 71

Calculating Capacity Statistics for Each Type of Plane The SAS System The MEANS Procedure Analysis Variable : totpasscap N size Obs N Mean Std Dev Minimum Maximum ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Large Medium Small 16 16 230. 13 32. 39 207. 00 290. 00 9 9 178. 56 11. 40 165. 00 188. 00 39 39 132. 64 18. 85 97. 00 150. 00 ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 72