MS**51 Analytics Using SAS
Assignment 2
• You must complete the assignment by yourself. Exchanging ideas with classmates is
encouraged, 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 each
question’s answer as SAS comment statements in the code, such as /*Task 1*/. Name
your code file as nnnnnnnn.pdf, where nnnnnnnn is your full name.
• You should set all irrelevant statements in your code as SAS comment statements. Be
aware that your SAS code created under a non-English operating system may contain
extraneous characters when viewed in an English operating system. You are responsible
for ensuring your submitted code is free of these characters. All extraneous characters in
your 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 section
of the course on Canvas. If you submit your file more than once, only the latest submitted
file will be marked. The assignment is due at 06:00 AM on 4 December 2024. Two marks
shall be deducted for every 1 minute or less late. The submission link will be closed at
06: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 the
zipped file for the following:
• Profile_2016.sas7bdat
• Flying_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 multiple DATA steps and SAS procedures for each task.
Task 1:
Define a SAS library named xxxxxxxx for the folder containing the three SAS data sets in your
SAS Studio account, where xxxxxxxx is your given name (or first name). Use only the first eight
letters if your given name is longer than eight characters. For example, if your name is CHEN
Da Wen, the library name should be ‘dawen’.
Task 2:
Write a SAS program for the following activities: • Create a new data set named Profile that includes all columns and rows from
Profile_2016. Store Profile in the SAS Work library.
• Add a new column named Tenure to Profile. Calculate a member’s tenure as full years
from their join date to 1 January 2017. For example, if a member’s join date is 1 August
1996, their tenure on 1 January 2017 would be 20.
• Print a report showing the range (maximum minus minimum) of Tenure values in the
Profile. 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 set
in a temporary SAS data set.
• Sort the observations in Flying_2016 by Member_ID and store the sorted data set in a
temporary SAS data set.
Task 4:
Write one SAS DATA step to reshape the sorted Flying_2016 data set by collapsing
observations with the same Member_Id into a single observation. Name this new SAS data
set Flying and store it in the SAS Work library. For each observation in Flying, create if needed
and keep only the following variables (the order of the variables is not specified) in the data
set:
Variable Name Description
Member_Id Member’s identity.
Air_CityU Number of times the member had flown with CityU Airlines (i.e.
Airline value = ‘CityU’) in 2016. Equal 0 if none.
Air_NonCityU Number of times the member had flown with non-CityU Airlines
(i.e. Airline value ^= ‘CityU’) in 2016. Equal 0 if none.
FlyBonus_Earned Total bonus points earned by the member from flying in 2016.
Task 5:
Write one SAS DATA step for all of the following activities:
• Merge the sorted Profile and Flying data sets by Member_ID. Name the merged data
set Profile1 and store it in the SAS Work library.
• 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 to
0.
Task 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 data
set Profile2.
• Sum the redemption points for each member in 2016. If a member had no redemption in
2016, set the sum to 0. Store these sums in a new column named Redeemed in Profile2. • Replace each member’s bonus point balance in Profile2 with a new balance value,
calculated as bonus point balance at the end of 2015 + bonus points earned from flying in
2016 – bonus points redeemed in 2016. {P.S. Some bonus point balances in the data set
may 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 requiring
further manual calculations:
a) The number of male members who flew only with CityU Airlines in 2016.
b) The number of members who had no bonus point redemptions in 2016.
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 of
the following bonus point balance groups:
a) 100,000 or lower
b) 100,001 – 300,000
c) 300,001 – 500,000
d) 500,001 or higher
You are not allowed to use the DATA step for this task. The report should look exactly like
this:
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 an
appropriate report procedure to write the results, including the formatted bonus point
balance values, to a SAS dataset. Finally, print that SAS dataset with the required title, labels,
and formats.}
-END-
請加QQ：99515681 郵箱：99515681@qq.com WX：codinghelp
