Question
Please complete the following code. Part 1: (For background info) . /* Run the libname.sas code here so that we can refer to that directory
Please complete the following code.
Part 1: (For background info) .
/* Run the libname.sas code here so that we can refer to that directory as sq in our work. Defines the path to your data and assigns the libref. */ %let path=~/ESQ1M6; libname sq "&path/data";
/* Preliminary work: Please run these queries to get started. Think about what they do as you run them. We want to get a subset of customer and transaction datasets since we will not need all of the data items. Use the newly created tables in your assignment queries. */
proc sql; describe table sq.customer; describe table sq.transaction; describe table sq.agegroup; quit;
proc sql; create table sq.cust as select FirstName, MiddleName, LastName, Gender, year(DOB) as BYear, Employed, Race, Married, Zip, substr(StateID,1,2) as State, AccountID, Income, CreditScore from sq.customer where AccountID is not null; quit;
proc sql; create table sq.tran as select TransactionID, month(DATEPART(DateTime)) as MTran, AccountID, Amount from sq.transaction;
Part 2:
/* - Report the first and last name, gender, employment status, marital status, race, and income of the customers in zipcode 02860 along with the age group they belong to. Filter out customers with no income data. Also, order by descending income. I have written most of the code. You just need to fill in the blanks (______). Hint: Zip is defined as a numeric variable. */ /* title "__________________"; footnote "______________"; proc sql; select ___,___,... from sq.cust a inner join sq.agegroup b on a.BYear between b.StartYear and b.EndYear where a.Zip = ____ and _____ order ________; quit; */
Part 3:
/* - Create a report that shows all the transactions of AccountID = 1010144135 for the first half of the year. Report all the fields from the cust table plus the transaction month and amount from the tran table. Sort the report by amount, then month. */
/* - Create a table that shows marital status by age group (column: Name in agegroup) COUNTs. I'd like to see how marital status is distributed across generations. Ignore the rows where married is null. Order by descending count for each married group so we can see the largest age group for each marital status. Hint: you will need cust and agegroup tables. Join them similar to question 1. Use a select * statement to print the contents of the table that you created. */
Part 4:
/* - How many New England customers who have NOT bought anything? First, create a table that has State from cust and Amount from tran tables while filtering on states. Think about how to join them. You want the rows where Amount is null. Then, report the counts by state using that new table. */
/* - Write ANY join query of your choosing. (Except, different from the above queries.) */
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started