SQL in Cartoons
OVERVIEW:
This seminar, using a cartoon format, starts as if you know nothing about SQL and progresses, using many small examples, to advanced SQL topics (the optimizer, indexes and increasing performance). Attendees will get a book detailing all examples in the seminar, code for all the queries in the seminar and a several hundred line program of SQL examples. The seminar can be reviewed at your home, to facilitate mastery of the material.
PROC SQL is very a powerful tool for querying data and is so versatile that it should be the entry point for learning SAS. If you know PROC SQL, you can use it in place of many other SAS procedures. It is the Swiss army knife of procedures and should be understood by every programmer.
In addition to SQL being important in SAS; SQL is the key to learning other software. SQL is the basis for queries in M.S. Access, Hadoop, Tableau,, Oracle, Terradata., Netizza, DB2, Business Objects, and other packages (even Excel)
SQL has been difficult to learn because SQL has been taught as a “black box”. The historical teaching method has been to have the student run dozens of SQL queries until s/he recognizes a pattern – but it has been impossible to explain why results come out as they do. This seminar shows a unique graphical model (a cartoon) of the previously undocumented PROC SQL internal processes and makes learning PROC SQL easy. It develops rules of how SQL works.
The major deliverable of this seminar is the graphical model/cartoon of the SQL process and rules for coding. We use that graphical model to develop rules for describing, and predicting, the SQL process. These descriptive rules translate directly into coding rules and allow a programmer to quickly code complex PROC SQL queries.
The Graphical Model of SQL Queries
Attendees will get a book detailing all examples in the seminar, code for all the queries in the seminar and a several hundred line program of SQL examples.
This seminar, using a cartoon format, starts as if you know nothing about SQL and progresses, using many small examples, to advanced SQL topics (the optimizer, indexes and increasing performance). Attendees will get a book detailing all examples in the seminar, code for all the queries in the seminar and a several hundred line program of SQL examples. The seminar can be reviewed at your home, to facilitate mastery of the material.
PROC SQL is very a powerful tool for querying data and is so versatile that it should be the entry point for learning SAS. If you know PROC SQL, you can use it in place of many other SAS procedures. It is the Swiss army knife of procedures and should be understood by every programmer.
In addition to SQL being important in SAS; SQL is the key to learning other software. SQL is the basis for queries in M.S. Access, Hadoop, Tableau,, Oracle, Terradata., Netizza, DB2, Business Objects, and other packages (even Excel)
SQL has been difficult to learn because SQL has been taught as a “black box”. The historical teaching method has been to have the student run dozens of SQL queries until s/he recognizes a pattern – but it has been impossible to explain why results come out as they do. This seminar shows a unique graphical model (a cartoon) of the previously undocumented PROC SQL internal processes and makes learning PROC SQL easy. It develops rules of how SQL works.
The major deliverable of this seminar is the graphical model/cartoon of the SQL process and rules for coding. We use that graphical model to develop rules for describing, and predicting, the SQL process. These descriptive rules translate directly into coding rules and allow a programmer to quickly code complex PROC SQL queries.
The Graphical Model of SQL Queries
- SQL as an environment
- Printing data
- Summary statistics
- Reports with grouping
- Calculating variables
- The Case Statement
- The internal processes of the Where versus Having clauses
- SQL joins (Inner, left, right, outer)
- SQL joins vs Data step Joins (merging tables)
- Sub Queries (correlated and uncorrelated and why correlated sub-queries are to be avoided)
- Multiple SQL joins and how the pivot table can be used to reduce run time
- Set Operators – (except, intersect, union, outer union and the corr option)
- Creating views with the SQL procedure
- Integrity constraints
- Dictionary tables
- Improving SQL Performance: Indexes and the where clause
- The SQL optimizer and the where clause
- Fuzzy merging
- Using SQL for Table Maintenance
Attendees will get a book detailing all examples in the seminar, code for all the queries in the seminar and a several hundred line program of SQL examples.
SAS Macros
This seminar, using a cartoon format, starts as if you know nothing about macros and progresses, using many small examples, to advanced macro topics (SYMPUT, SYMGET, CALL EXECUTE and driver files). Attendees will get a book detailing all examples in the seminar, code for all the examples in the seminar and a several hundred line program of macro examples. The seminar can be reviewed at your home, to facilitate mastery of the material.
Macros are used in almost every SAS corporate/consulting environment and are used to make SAS code simpler and easier to maintain. Additionally, macros are used to automate processes and make processes/programs “production”. Without knowledge of SAS macros, a new employee cannot maintain, or sometimes even read, legacy SAS code (legacy code is code written by the last person who last had your job or by the person who sits in the next cube).
Learning the SAS macro language is often a time consuming, and stressful part of the working SAS world. While starting at zero, This seminar covers advanced topics and experienced professionals have often found it useful to attend.
Almost all macro problems fall into one of these 4 types and the cartoons illustrate all skills. We start with sample Code for how to perform the 4 common macro tasks. This can be used as “starter code” when on a job.
Below is a detailed list of topics covered in the macro seminar
Macros are used in almost every SAS corporate/consulting environment and are used to make SAS code simpler and easier to maintain. Additionally, macros are used to automate processes and make processes/programs “production”. Without knowledge of SAS macros, a new employee cannot maintain, or sometimes even read, legacy SAS code (legacy code is code written by the last person who last had your job or by the person who sits in the next cube).
Learning the SAS macro language is often a time consuming, and stressful part of the working SAS world. While starting at zero, This seminar covers advanced topics and experienced professionals have often found it useful to attend.
Almost all macro problems fall into one of these 4 types and the cartoons illustrate all skills. We start with sample Code for how to perform the 4 common macro tasks. This can be used as “starter code” when on a job.
- Wrap code in a macro and call it with different parameters
- Automate jobs (have your program write your program)
- %DO loop and the &&STATE&i
- automate with SQL and the %SCAN loop
- Conditionally executing code with a %IF
- Making “macro code snippets” (macros that generate part of a SAS statement)
Below is a detailed list of topics covered in the macro seminar
- Components of the SAS system (the SAS supervisor)
- Tokenization and the 4 common types of tokens
- The three compiles/executes that happen when running a macro program
- The Macro Symbol Table and the Macro Catalog
- Moving tokens into and out of the Macro Symbol Table and the Macro Catalog
- Evaluating multiple ampersands (&StateNo &&StateNo &&&StateNo &&&&StateNo)
- The effect of single vs double quotes
- Global vs Local macro variables
- Using macros to automate a process (two common ways to automate a program)
- Rules that are applied to tokens as they flow into the Macro Catalog
- Rules that are applied to tokens as they flow out of the Macro Catalog
- CALL SYMPUT Basics
- SYMGET Basics
- An example of CALL SYMPUT and SYMGET
- CALL EXECUTE: automating a series of reports with Call Execute and a 'driver" file
- The rules for the parameters in: CALL SYMPUT, SYMGET and CALL EXECUTE
- Single vs double quotes in CALL EXECUTE parameters
About the Instructor
Russ Lavery is a frequent and multiple award-winning presenter at SAS user groups. He has been a technical reviewer on five books on SAS and statistical topics. He has over 25 years of experience using SAS and is still studying. Russ is a contractor and lives outside Philadelphia, PA, where he occasionally teaches as an adjunct in the Drexel University analytic program and dances frequently.
|