Authors: Benjamin Attle and Christopher Etherington

Important Packages

install.packages(“dplyr”) - allows you to

install.packages(“magrittr”) - allows you to

install.packages(“xlsx”) - allows you to export to Excel

Subset

Data <- select(CurrentDataSet, Column1, Column2 etc) - select only the chosen columns from your data

Data <- subset(CurrentDataSet, Column1 = “ColumnDetail”) - select only where the column = a certain value/word e.g. Market = Care

Data <- subset(CurrentDataSet, Column1 = “ColumnDetail”, select = c(Column1,Column2)) - select only where the column = a certain value/word e.g. Market = Care

Data <- subset(select(CurrentDataSet, Column1, Column2), Column1 = “ColumnDetail”) - select the chosen columns from your data where the column = a certain value/word

Merge

The Merge function in R is similar to the join function in SAS - it is used to join two tables together on a key

TestDataSet <- merge(CurrentDataSet1, CurrentDataSet2, by.x = “DataSetKey1”, by.y = “DataSetKey2”)

The above is where the key in the two datasets is a different name. If they are the same e.g both are “pivotal_number”, then replace by.x with by = “pivotal_number” and drop the by.y

As with SAS, there are different ways to join/merge a table - please see below

Outer join: merge(x = “DataSetKey1”, , y = “DataSetKey2”), by = “CustomerId”, all = TRUE)

Left outer: merge(x = “DataSetKey1”, , y = “DataSetKey2”), by = “CustomerId”, all.x = TRUE)

Right outer: merge(x = “DataSetKey1”, , y = “DataSetKey2”), by = “CustomerId”, all.y = TRUE)

Cross join: merge(x = “DataSetKey1”, , y = “DataSetKey2”), by = NULL)

You can also merge on multiple columns by giving a vector, e.g., by = c(" Column1“,” Column2“)

You can also select specific column names from your merged datasets - see below

TestDataSet <- select(merge(CurrentDataSet1, CurrentDataSet2, by.x = “DataSetKey1”, by.y = “DataSetKey2”), “Column1” “Column2”)

Connect to SSMS

install.packages(“RODBC”) library(RODBC)

connect <- odbcConnect(“mydsn”,uid=“crystal”,pwd=“report5”)

Dataframe<- sqlQuery(connect,“select agent = rn_descriptor ,mon_req = daily_call_level_mon ,tue_req = daily_call_level_tue ,wed_req = daily_call_level_wed ,thu_req = daily_call_level_thu ,fri_req = daily_call_level_fri from [CRM_PROD_Online_ED].[dbo].[Employee]”)

Unlike with SAS, in Rstudio, when renaming column headings, the name you intend to call the column is inserted first followed by an “=” and finally, the current column name as found in the dataframe.

Case When

TBC

Join

DataSet <- left_join(CurrentDataSet, select(sas_companies, pivotal_number, company_name, post_code), by = ‘pivotal_number’)

Embed Image in Email

install.packages(“magrittr”)

library(magrittr)

Get a nicely formatted date/time string

current_date_time <- add_readable_time()

install.packages(“blastula”)

library(blastula)

Create an HTML fragment that contains an image

img_file_path <- (“C:/Users/ImageFileLocationjpg”)

img_file_html <- add_image(file = img_file_path)

Include the image in the email message body by simply referencing the img_file_htmlobject

compose_email(body = “Hello! Take a look at this image:

{img_file_html}

It is of insert subject of image.“) %>% preview_email()

Import SAS Files

install.packages(“sas7bdat”)

library(sas7bdat)

mydata <- read.sas7bdat(“T:/MI/SAS/Database Files/Database/appointments.sas7bdat”)

install.packages(“haven”)

library(haven)

mydata2 <- read_sas(“T:/MI/SAS/Database Files/Database/appointments.sas7bdat”)