MS3251 Analytics Using SAS

MS3251 Analytics Using SAS

Assignment 2

• You must complete the assignment by yourself. Exchanging ideas with classmates isencouraged, but you must not cross the line between discussion and collaboration. Showing your work to your classmates is a non-acceptable collaboration. All identified collaboration work will have a 0 mark.

• Complete all questions. Put all of your SAS code into one PDF file. You must mark eachquestion’s answer as SAS comment statements in the code, such as /*Task 1*/. Nameyour code file as nnnnnnnn.pdf, where nnnnnnnn is your full name.You should set all irrelevant statements in your code as SAS comment statements. Beaware that your SAS code created under a non-English operating system may containextraneous characters when viewed in an English operating system. You are responsiblefor ensuring your submitted code is free of these characters. All extraneous charactersinyour submitted code will be considered errors. Utilising codes not included in the course notes will not be accepted as correct answers,even though they are not wrong.

• When running in SAS OnDemand Studio, your submitted SAS code should be error-free.
• You must submit your code file via the link Assignment 2 under the Assignments sectionof the course on Canvas. If you submit your file more than once, only the latest submittedfile will be marked. The assignment is due at 06:00 AM on 4 December 2024. Two marksshall be deducted for every 1 minute or less late. The submission link will be closed at06:30 AM on 4 December 2024. Submission by other methods will not be accepted.

Tasks Download Assignment2.zip from the Assignments folder under the Files section. Expand theipped file for the following:rofile_2016.sas7bdatFlying_2016.sas7bdat

Redemption_2016.sas7bdat

• Variables_Disctionary.xlsx

The variable descriptions can be found in Variables_Dictionary.xlsx. Do not change the contents of these data sets unless you are told to do so explicitly. You are asked to write an SAS program for the tasks below. Unless stated explicitly, you can deploy Task 1:Define a SAS library named xxxxxxxx for the folder containing the three SAS data sets in yourAS Studio account, where xxxxxxxx is your given name (or first name). Use only thefirst eightollowing activities:• Create a new data set named Profile that includes all columns and rows fromProfile_2016. Store Profile in the SAS Work library.

Add a new column named Tenure to Profile. Calculate a member’s tenure as full yearsfrom their join date to 1 January 2017. For example, if a member’s join date is 1 August1996, their tenure on 1 January 2017 would be 20.

• Print a report showing the range (maximum minus minimum) of Tenure values in theProfile. There is no need to include the report in your submitted code.Task 3:Write a SAS program for the following activities:

• Sort the observations in Profile by Member_ID.
• Sort the observations in Redemption_2016 by Member_ID and store the sorted data setin a temporary SAS data set.

• Sort the observations in Flying_2016 by Member_ID and store the sorted data set in atemporary SAS data set.

Task 4:

Write one SAS DATA step to reshape the sorted Flying_2016 data set by collapsingobservations with the same Member_Id into a single observation. Name this new SAS datathe dataset:Variable NameDescriptionMember_IdMember’s identity.

Air_CityUNumber of times the member had flown with CityU Airlines (i.e.Airline value = ‘CityU’) in 2016. Equal 0 if none.Air_NonCityUNumber of times the member had flown with non-CityU Airline.e. Airline value ^= ‘CityU’) in 2016. Equal 0 if none.Merge the sorted Profile and Flying data sets by Member_ID. Name the merged data

• Keep all columns from both data sets in Profile1.For members who did not fly in 2016, set Air_CityU and Air_NonCityU to 0.
• For members who did not earn bonus points from flying in 2016, set FlyBonus_Earned toTask 6:

Write one SAS DATA step for all of the following activities:

• Merge the sorted Redemption_2016 and Profile1 by Member_ID. Name the merged dataset Profile2.

• Sum the redemption points for each member in 2016. If a member had no redemption in2016, set the sum to 0. Store these sums in a new column named Redeemed in Profile2.• Replace each member’s bonus point balance in 代写MS3251 Analytics Using SAS  Profile2 with a new balance value,calculated as bonus point balance at the end of 2015 + bonus points earned  flying in2016 – bonus points redeemed in 2016. {P.S. Some bonus point balances in the datasetmay be negative. Keep them in the data set. No adjustment is required.}

• Each Member_ID appears only once in Profile2.Drop the variables Date and Redeemed_point from the sorted Redemption_2016 in

Profile2.Task 7:

Write a SAS program to report the following statistics and information without requiringfurther manual calculations:

2016. a) The number of male members who flew only with CityU Airlines in 2016.
2017. b) The number of members who had no bonus point redemptions in 2016.
2018. c) The top 1000 bonus points earners in 2016.There is no need to include the reports in your submitted code.Task 8:Write a SAS program to generate a report on the number of members in Profile2 for each ofthe followingbonus point balance groups:

1. a) 100,000 or lower
2. b) 100,001 – 300,000
3. c) 300,001 – 500,000
4. d) 500,001 or higher

You are not allowed to use the DATA step for this task. The report should look exactly likethis:There is no need to include the reports in your submitted code.{Hint: First, create a user-defined format for the bonus point balance groups. Next, use anappropriate report procedure to write the results, including the formatted bonus pointbalance values, to a SAS dataset. Finally, print that SAS dataset with the required title, labels,and formats.}

-END-