SQL Case Study: Prepare and Analyze United States Airport Claims and Enplanement Data

[#1]

Have you ever been to an airport in the United States and your luggage was lost or you experienced an incident that involved theft, property damage, personal injury or even death? If so, you can file a claim and a record of that incident will be documented by the United States Transportation Security Administration (TSA).

Now have you ever wondered how many claims are filed a year? Or which airports typically have a claim filed? What about the percentage of passengers that have to file a claim based on total enplanements? In this case study you will apply your knowledge from the SAS SQL Essentials course to Access, Explore and Prepare TSA Claims and FAA Enplanement data from 2013 to 2017. Once you are complete with data preparation, you will run a SAS program to analyze and export the results to a static HTML dashboard:

Using the dashboard, you can answer business questions like:

• How many total enplanements are in final report?
• What is the percentage of claims filed by enplanements?
• What is the average time in days to file a claim?
• What is the most frequent location identified for claims? How many claims were filed for this location?
• Which airport with more than 10 million passengers has the highest percentage of claims?

When working on this case study you might be thinking "what do I do if I get stuck?" That's an excellent question! Let's discuss the ways you can attempt the case study to best suit your specific skill set:

Advanced Level

If you feel comfortable with the topics in the SQL 1: Essentials course and want to treat this as a real-world problem, read Section 1.3, “Advanced Level,” of the PDF and begin. During the process, feel free to use your notes, Google, or SAS documentation.

Intermediate Level

If you think you might need a bit of assistance in the case study, read Section 1.4, “Intermediate Level,” of the PDF for a guide. This section does not give you the solutions, but instead it provides a roadmap on how to solve the problem. If you are stuck on a specific task, you can find the solution in Section 1.5, “Beginner Level.”

Beginner Level

If you are not familiar with SQL and want to use the case study as a walk-through demo, feel free to do so. You can read Section 1.5, “Beginner Level,” and follow a roadmap to solve the problem using a suggested solution for each task. After you run through the case study as a demo, we recommend that you go back and attempt it on your own.

If you have any questions regarding the case study, if you came up with different solutions and want to show them off, or if you want to share additional visualizations, post in the SAS Training Forum. We would love to hear from you!

To attempt this case study, you will need to download the:

• SAS SQL Essentials Case Study PDF
• casestudy_cre8data.zip file

Both are attached below.

Be curious. What other questions can you answer with this data? 

[donathomas288]

I have downloaded this pdf and run the casestudy_cre8data. here it is mentiontioned that after run the %let statement with the path of the writable location automatically following files will be created: 
 AnalysisProgram.sas 
 StarterProgram.sas 
 - SAS Tables 
 1. work.claimsraw 
2. work.boarding2013_2016 
3. work.enplanement2017

But i didnt get the analysis program.sas and starterprogram. i am using sas studio. why didnt i get those two files?

[idsree3]

Please check the path you are giving in %let statement. It worked fine for me in sas studio. 

[u445330633]

SAS® SQL 1: Essentials
Appendix A
Case Study  - Plz I want advanced SQL solution for this.

[Panagiotis]

Hi @u445330633 ,

Download the PDF and go to page A-16 the Beginner Level section. That is a solution to the case study. Remember, there are a variety of solutions, and the PDF provides one. If you solve it another way feel free to post it here for others!

- Peter