SQL_Eberhardt - Data Step: the Sequel Oct 24, 2006 Peter...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Data Step: the Sequel Oct 24, 2006 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Starring..... select from where Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL And in supporting roles... Order by Group by Having Quit Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL What is SQL? Structured Query Language Developed by IBM Standardized public domain language Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Why SQL? Standardized public domain language Portable skill Concise language Part of Base SAS Data Management tool in Enterprise Guide Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Terminology Tables (datasets) Rows (observations) Columns (variables) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Agenda SQL Basics query and reporting Data Management Summarizing Data Macro Interface Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics PROC SQL; SELECT column list FROM table list WHERE condition list ORDER BY column list ; quit; Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics PROC SQL; SELECT * FROM table list WHERE condition list ORDER BY column list ; quit; Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics !" Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics # % !" &' $ $ Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics ' ' !" ( + )* ( ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics $ , % ," + . . . . . ( # # # # # # + + + % , , , , , )* -%/0 /0 -%/0 /0 -%/0 /0 -%/0 /0 -%/0 /0 ( ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics ! + 12 '1 ( ' + + ," 12 %3 %+' ' %+' )* 3 4" ( ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics $ , % !" + + # # # # # # + %+' + + % . . . . 4"3 4"3 . ( -%/0 /0 , -%/0 -% , -%/0 /0 , -%/0 /0 3 , -%/0 /0 )* ( ) , Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL Basics Easy to create simple Queries and Reports Can dynamically create new columns Can dynamically sort For more advanced reporting, stick to PROC REPORT Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Create a new table Drop a table Types of joins Join 2 tables Join 4 tables Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Create a table 5 6 5 !" ,6 -( ( )) % 8 7 , 6 ( 6 %37 3) 3) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Create a table 5 7 !" ,-( ( )) % 8 + ( 6 , 6 %37 + 3) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Drop a table 5 6 7 6 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Drop a table 5 7 6 , 6 + %, 9 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining tables inner join left join right join full join Tables do not need to be sorted Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining Tables Join Types Inner Join keep what is common to both Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining Tables Join Types Left Join keep all of the `left' table and what is common to both Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining Tables Join Types Right Join keep all of the `right' table and what is common to both Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining Tables Join Types Outer Join keep all of the both tables Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Join 2 tables - orders and orderdetails inner (or natural) join use table aliases Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 2 Tables orderID orderID orders orderdetails Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 2 Tables 5 5 , 7 6 " ; 6 : 6 5 ,!" " 6 !" % " Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 2 Tables 5 +' ' % 6 % 6 ; < + ( 6 %3 (= $% (= ) % 5 ; $% +' '3) ) !" % < * !" , % 6 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Join 4 tables - orders, orderdetails, customers, and products inner (or natural) join use table aliases Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables orderID customerID orderID productID orders customerID orderdetails productID customers Peter Eberhardt products Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 6 " " " " % !" !" " " " 6 ; , 6 0 : > 6 5 8"0 " !" !" # ,- % % % " " " Fernwood Consulting Group Inc. Peter Eberhardt DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 5 # 6 % 6 % !" 6 % !" 6 Peter Eberhardt Fernwood Consulting Group Inc. . % 6 !" % 6 < : ; !" % % % DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 < ; < # 0 +/ ( 6 6 (= ) 6 % 5 %3+' ' $% (= $% /3) ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 6 % ; +/ 5 < ; < 6 ? +0 ( ; 6 6 %3+' ' +/ (= $% 03) ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 6 % ; +0 5 < ; < 6 > ; 6 +0 ( 6 +0 (= %3 $% 7 +' '3) ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 6 % ; +0 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Joining 4 Tables 5 6 " " " " % !" !" " " " 6 ; , 6 0 : > 6 5 8"0 " !" !" # ,- % % % " " " Fernwood Consulting Group Inc. Peter Eberhardt DATA Step: the SEQUEL SQL: Data Management Who is missing left join orders and customers is null Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Join Types Left Join keep all of the `left' table and what is common to both Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Left Join 5 # : < , , * - # " ,- "5 !" " 6 + % " + # : + !" Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Left Join 5 5 , # % !" 6 + % * % !" 6 + % Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Left Join 5 5 < 9 , < 6 # + 6 3 9 ( (9 % % ) + % ) Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Data Management Left Join 5 6 % 5 , 6 @ % * +' ' Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Summary Functions Basic descriptive statistics avg, sum max, min nmiss std, var count, count distinct GROUP BY Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Summary Functions A 6 $ 6 ( # ;<( # ( # # " < 6 6 Fernwood Consulting Group Inc. % $ % % ';<. % : # 6 ) % % % 6 ) ;< /B" 6 +) 4" 6 /B" !" !" + % " + Peter Eberhardt DATA Step: the SEQUEL SQL: Summary Functions + ( # ( # ( # $ # !" % /B" % /B" % +) /B" 6 % 6 # +) 6 % +) 6 % 6 $ 6 # # Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Macro Variables Automatic macro variables are created &sqlrc - an error code &sqlobs - number of rows returned for users of SAS/ACCESS there are error message variables Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Macro Variables 5 , & ; 6 C # 6 6 D D !" A ! + E F A + G E F 6 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Macro Variables D ' # (1' ) 6 5 # ,5 D % F1' , , % F # 1' 6 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Macro Variables D5 D # F D D 5 # , , H B $ D / 0 $ 2 $ , 9 # %# $ F 1' F6 , 6 Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL SQL: Macro Variables D5 D D 5 , , < < < / $ 2 % $ ! 9 % IJJ * $ < F6 6 < $ D D Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Review SQL Basics query and reporting Data Management Summarizing Data Macro Interface Peter Eberhardt Fernwood Consulting Group Inc. DATA Step: the SEQUEL Peter Eberhardt peter@fernwood.ca Peter Eberhardt Fernwood Consulting Group Inc. ...
View Full Document

Ask a homework question - tutors are online