# Revolution R

### If and Else function in R

I have discussed that how to use if and else statement in R in my previous article.

In a real time coding, we will be expecting a short form of code which should give the same expected result.

Vectors are basic building block of R Programming and will be used as input.

To apply the same if and else logic for the vector input, we can use if and else function which will give the same result but as a output vector.

Syntax:

Ifelse(test_expression,x,y)

The output of the function could be a vector. In the above syntax, test_expression is a condition which we can apply into the input vector and x is nothing but a custom value or expression, in logical it is called TRUE. If the condition is satisfied then x (TRUE) will display, you can replace x with any value. Here y is FALSE, if the test_expression is not satisfied then y will display.

Example,

number = c(3,5,7,10)
ifelse(number %% 2 == 0,”even”,”odd”)

Logically the result would be FALSE, FALSE, FALSE, TRUE.

### If and Else statement in R

As we know, decision making is an important part for every programming. Almost all the projects make use of this decision making logic and sometimes this is very mandatory as well. Considering this importance, R language has if..else statement.

Probably we are in situation to run the set of codes when the condition is match. In that case, we can use if statement. It is very easy to use in R as like other programming language.

The General flow of “IF” statement,

Syntax:

if (test_expression) {
statement
}

Examples:

Example 1

x <- 10
if(x > 0){
print(“Positive number”)
}

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)}

But in a real time scenario, sometimes we need to run the set of codes when the condition is not match. If that is the case then we need to use if and else statement.

Syntax:

if (test_expression) {
statement1
} else {
statement2
}

Examples:

Example 1

x <- -1
if(x > 0){
print(“Positive Number”)
} else {
print(“Negative number”)
}

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)} else

{ print (“Today is not Monday”)}

We can use Nested if and else statement,

Syntax:

if ( test_expression1) {
statement1
} else if ( test_expression2) {
statement2
} else if ( test_expression3) {
statement3
} else
statement4

Examples:

Example 1

x <- 0
if (x < 0) {
print(“Negative number”)
} else if (x > 0) {
print(“Positive number”)
} else
print(“Zero”)

Example 2

X<-weekdays(Sys.Date())

if(X == “Monday”)

{ print(“Today is Monday”)} else if (X==”Tuesday”)

{ print (“Today is Tuesday”)} else if (X==”Wednesday”)

{ print (“Today is Wednesay”)} else if (X==”Thursday”)

{ print (“Today is Thursday”)} else if (X==”Friday”)

{ print (“Today is Friday”)} else if (X==”Saturday”)

{ print (“Today is Saturday”)} else

{print (“Today is Sunday”)}

### R Plots in SQL Server 2016

As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.

It makes the R integration with SQL Server 2016 is easy.

As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.

Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.

I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.

Solution 1:

Step 1: Create a sample database and table. Insert some values into the table.

Step 2: Do the modification on the table name and column name and run it.

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df\$Values);

dev.off();

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 3: Make sure that you are getting an varbinary result as a output.

Step 4: Wrap the above code within stored procedure, like below and check the result again.

CREATE PROCEDURE Sp_Test1

As

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df\$Values); #column name

dev.off();

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 5: Use below bcp command and convert the Varbinay into jpeg format.

bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name>  -U <user name> -P <password>

Step 6: Verify the plot in a above image.

Solution 2:

Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.

Step 2: Verify the plot.

Solution 3:

We can export the result as an image file without using bcp tool itself.

Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image. Step 2: Run the script and check the above specified location. Image file will be available with plot. ### SQL Server 2016 RC2–Enhancements in R Integration

Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.

Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.

Look at the below changes in the installation center.

New feature to create a standalone R Server.  Confirmation to install Microsoft R Open. ### Issues in R Package in SQL Server 2016

I have got a below error when I try to use one of the R package “ggplot2”. It says that there is no package called ggplot2 even though I installed the ggplot2 package in R.

Message from R when I install ggplot2 package, The above message clearly explaining that the package is not installed on defined path, it has been downloaded on temporary directory. It means there was no proper installation happened in R. This was the reason for error in SQL Server.

Follow the below steps to sort out the issue,

Use “.libPaths” to define the library path before install a package in R.

Command is,

> .libPaths (.libPaths()[2:3])

> install.packages(“ggplot2”)

Package will be installed like below, Now run the same SQL Script with R, this time it will be executed without an issue.

### Install R Studio 0.99.491

You can download the latest R Studio 0.99.491 and install for FREE in this link. However if you need support then you need to agree to a monthly term to support you and your production system, and you can compare it from this link about the pricing for your desktop and server.

I have downloaded the RStdio-0.99.491 and have started installing and provided you the below screenshots for your reference. In here, you have option to choose your folder and it also gives you the amount of space required for this installation. I know, I am running out of space, but I am doing this in my virtual machine. Here you get option to add to the start menu and create shortcuts. I chose the default options in below screenshot. As you can see below, the extraction and installation takes place for about couple of minutes. That’s it. We are now done with the installation of R Studio. 