Assignment 1

Bank Marketing Case Study: loading and merging data

Learning outcomes

1. Load data using input Files in Various Formats to combine information from many data domains and sources


2. Rename columns and convert column types from character to numeric to prepare for merging


3. Merge sas datasets to obtain a datawarehouse ready for analysis

Introduction

The head of Marketing wants to know which customers have the highest propensity for buying a Certificate of Deposit (CD) from the institution. The goal of this assignment is to create part of an analytical data mart by combining information from many data domains and sources.

Q1. Load data from customer_banking_info_promo.xslx

• define the library name "mylib" and specify its location using libname


• Use proc import DATAFILE to import customer_banking_info_promo.xlsx into a sas dataset named customer_banking_info_promo under mylib


• Print the first five rows of the dataset add (obs=5) at the end of proc print.

libname mylib '/folders/myfolders/Assignments';

Q2. Examine the variable Customer ID. Check the type and format.

• Use proc content procedure to examine the variables and their types. This will also print more details. ref:http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#p120panelmbpren1m0j2n77s9f67.htm
  

Q3. Column deletion/renaming

Look at the description of the different columns here:https://archive.ics.uci.edu/ml/datasets/bank+marketing

duration: last contact duration, in seconds (numeric). Important note: this attribute highly affects the output target (e.g., if duration=0 then y='no'). Yet, the duration is not known before a call is performed. Also, after the end of the call y is obviously known. Thus, this input should only be included for benchmark purposes and should be discarded if the intention is to have a realistic predictive model.

• Within a data step, perform the following:
  
  
  • keep the output dataset name same as input (customer_banking_info_promo)
  
  
  • Rename "customer_id2" to customer_id
  
  
  • drop the column "duration" from the dataset.
  
  
  
  


• print the first 5 observations in the dataset

Q4. Load data from customer_banking_info.csv

load the data and print the first five rows.

Q5. Renaming columns

• use proc contents to examine the list of variables as before. You will see that customer_id1 is numerical with len=8. This is important to check as this column will be used to merge the datasets.


• Within a data step, perform the following:
  
  
  • keep the output dataset name same as input (customer_banking_info)
  
  
  • Rename "customer_id1" as customer_id
  
  
  
  


• print the first 5 observations in the dataset

* use proc contents here;

* code to rename columns here and print;

Q6. SAS data from customer_demographics.sas7bdat

• print the first 5 rows of customer_demographics.sas7bdat


• use proc contents and examine the list of variables. What is the type of customer_id

* code to print here;

* use proc contents here;

Q7. Convert from character to numeric type

Before merging multiple datasets, the common column between the datasets should be of the same type.
In customer_banking_info_promo, customer_id is defined as character. you are given a sample data step code to run:

• the output dataset name customer_banking_info_promocv


• to convert customer_id to numeric variable, we use the input function. reference:http://support.sas.com/kb/24/590.html
  


• sample statement: numeric_var = input(char_var, 8.);


• rename the numerical column as customer_id: rename old_name= customer_id;

check the customer_id variable type again by using proc contents or proc means to see the list of numerical variables

*check type again;

Q8. Data Merging

• Join the three sources of data into a single SAS data set.
  
  
  • sort each of the datasets by customer_id
  
  
  • merge the three datasets using the merge function within a data step. name the new dataset as "customer_all"
  
  
  • print the first five observations.
  
  
  
  

Refer to

section 10.9 Controlling Observations in a Merged Data Set Chapter 10 - Subsetting and Combining SAS Data Sets Learning SAS by Example: A Programmer's Guide, Second Edition

* code for merging goes here;

* print data;

Q9. Reorder the columns

Reorder the column names in customer_all so customer_id appears first.

• reorder columns, use the same dataset name for input and output customer_all. Ref:http://support.sas.com/kb/8/395.html
  


• in case you use output dataset name different than input one, you can rename the sas Data set using change function, Ref;http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001397454.htm
  


• print the first five observations in customer_all dataset.

*reorder;   /* to rename dataset if needed;  proc datasets library=mylib;    change customer_all2=customer_all; run; */  * print code here;