Problem 0: This part needs to work before you go any further. You need three SAS statements: A %LET statement that links the program to the folder Other Data Files, where the Excel workbook Homework 2 2023.xlsx is located. The statement OPTIONS VALIDVARNAME=V7; to force all variable names imported from Excel to SAS standards. A LIBNAME statement with an XLSX engine that goes to the Excel workbook Homework 2 2023.xlsx. There are examples of this at the beginning of Lecture 7 (Character Functions Part One) and in Lecture 8 (IF-THEN-ELSE and CASE-WHEN-THEN). Run this code to see that it works, and make sure it does before you do Problem 1. Problem 1: (20 pts) The data file Prob1 as set up in Excel is below: STA 616 Homework 2 Part A Due Friday March 3 Page 3 Here: ID is a character variable. The first two letters indicate location: UK = US military base in the United Kingdom, PR = Puerto Rico, AK = Alaska ZIP is supposed to be a 5-digit ZIP code. Both the UK and PR ZIP codes should start with zeroes. Q1 is a response to a question. The possible responses are: SA = Strongly Agree, A = Agree, N = Neutral, D = Disagree, SD = Strongly Disagree, NA = Not applicable (missing) Q2 is a numerical response to a question. The possible responses are: 1 = Strongly Agree, 2 = Agree, 3 = Neutral, 4 = Disagree, 5 = Strongly Disagree, 9 = No response (missing) (a) Create a temporary data set Prob1 from the permanent SAS data set Prob1 in the permanent SAS library. Use this temporary SAS data set for the rest of Problem 1. (b) Use PROC FORMAT to create a format named Qchar for the values SA, A, N, D and SD for question Q1. (c) Use PROC FORMAT to create a format named Qnum for the values 1, 2, 3, 4 and 5 for question Q2. (d) Use PROC PRINT to list the permanent SAS data set with the formats applied to the variables. Add a title to this PROC PRINT. (e) In a SAS data step create a temporary data set Prob1before from the permanent SAS data set Prob1. (f) In PROC SQL create a SAS temporary data set Prob1After that includes all the following modifications from the temporary SAS data set Prob1Before: Create a variable OBS that is the observation number for the observation in the set. This variable OBS should be the first variable in the data set. From the ID variable use a character function to create a variable statecode that is equal to the first two characters of the ID number. Add a label to statecode so that the variable appears as State Code. Change ZIP to a character variable where the leading zeroes are added to the ZIP codes. For example, the ZIP code for observation PR033 should be 00911, and the ZIP code for observation UK019 should be 09408. STA 616 Homework 2 Part A Due Friday March 3 Page 4 Q1 is now a numeric variable, where SA is replaced by 1, A is replaced by 2, N is replaced by 3, D is replaced by 4, ND is replaced by 5, and NA is missing. Apply the appropriate format from the PROC FORMAT to Q1 in the new data set. Change the 9s in Q2 to missing but keep Q2 a numeric variable. Apply the appropriate format from the PROC FORMAT to Q1 in the new data set. Fill in the blanks below to create a variable Q2REV where the values of Q2 are reverse scored. This means that the responses will still go from 1 to 5, except that 1 = strongly disagree, 2 = disagree, 3 = neutral, 4 = agree, and 5 = strongly agree. Hence, in the data set all the 1s become 5s, all the 2s become 4s, etc. Note well: I want only one WHENTHEN with this CASE, not five! CASE WHEN ___________ THEN ___________ ELSE _________________ END AS Q2REV HINT: Think of the values as points where the x-coordinate is Q1 and the y-coordinate is Q1REV. Then you have a table of values: X = Q1 Y = Q1REV If you were to plot these points on graph paper with an x-axis and y-axis, what kind of pattern do the points have? Also, what is the equation of this pattern? 1 5 2 4 3 3 4 2 5 1 Write a new format Q2numrev for the reversed scored Q2REV and add the format to the PROC FORMAT in Part A. Rerun the PROC FORMAT with the new format. Apply the format Q2numrev to the variable Q2REV in the new data set. Also add a label to Q2REV so that the variable appears as Q2 Reverse Scored. Be sure that the variables in Prob1After are saved in the following order: OBS, ID, statecode, ZIP, Q1, Q2, Q2REV. List the variables in Prob1After with a * option and titles on the listing. STA 616 Homework 2 Part A Due Friday March 3 Page 5 (g) Use PROC DATASETS with the NOLIST option in the first statement to clear the formats (but not the labels!) from the variables in Proc1After. For an example see: http://studysas.blogspot.com/2009/05/how-to-delete-or-remove-previously.html . (h) Use PROC PRINT to list the data set from (g) with the removed formats. Include the LABEL options (to keep the labels) and the NOOBS option (to suppress the PROC PRINT-generation observation numbers).