Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

4
Handling Dates, Strings, Numbers

So far, we have learnt how to import data and create your own data in R and SAS, along with data inspection and cleaning. Here, we will learn how to work with different kinds of data, for example, dates, strings and numbers and how to convert one data format to another in both R and SAS. This includes handling numeric data, manipulating string/character variables (i.e. by extracting a substring of a string variable), handling different types of date format and numeric calculations with dates (i.e. difference between dates) as well as categorical data.

4.1 Working with Numeric Data

Integral types represent only whole numbers (positive, negative, and zero), and nonintegral types represent numbers with both integer and fractional parts.

4.1.1 Handling Numbers in SAS

Assigning Numeric Values to Variables

data ajay2;
a="1234567";
Run;

proc print data=ajay2;
Run;
Obs     a
1     1234567

We multiply the character variable by 1 to try to make it numeric in SAS.


data ajay21;
set ajay2;
a2=1*a;
run;
 
proc print data=ajay21;
Run;
Obs     a           a2
1       1234567     1234567

proc contents data=ajay21;
run;
#     Variable     Type     Len
1     a            Char     7
2     a2           Num      8

We also use input to convert character to numeric variable, but in SAS we need to specify the correct type of informat as well.

COMMA ELIMINATION USING INFORMAT

DOLLAR AND COMMA ELIMINATION USING INFORMAT

NUMERIC TO CHARACTER USING PUT

data test ;
prodID = 011 ;
result = put(prodID , 3.) ;
run ;

proc print data=test;
run;

Obs     prodID     result
1       11         11

proc contents data=test;
run;

#     Variable     Type     Len
1     prodID       Num      8
2     result       Char     3

4.1.2 Numeric Data in R

Using R As Calculator

Assignment Of Numeric Values

Arithmetic with Numeric Variables

Numeric Vector

Naming A Vector

Convert Numeric to String

4.2 Working with Date Data

Dates are a special case of numeric data because dates have multiple formats. Date variables can pose a challenge in data management. Date data are quite critical for industries like finance, telecom, sales etc. R and SAS provide several options for dealing with date and datetime data.

4.2.1 Handling Dates in SAS

SAS date value is a value that represents the number of days between 1 January 1960, and a specified date. Dates before 1 January 1960, are negative numbers; dates after are positive numbers. Various SAS language elements handle SAS date values: functions, formats and informats.

READING DATE

The format used here is date9. which is used when specifying the first three letters of month, in total making it nine characters.

data new1;
maindate = "12JUL2017";
date = input(maindate,date9.);
format date date9.;
Run;

proc print data=new1;
Run;

Obs   maindate     date
1     12JUL2017    12JUL2017

proc contents data=new1;
run;

#     Variable     Type     Len     Format
2     date         Num      8       DATE9.
1     maindate     Char     9

DATE9. is an INFORMAT. In an INPUT statement, it provides the SAS interpreter with a set of translation commands it can send to the compiler to turn your text into the right numbers, which will then look like a date once the right FORMAT is applied.

INFORMAT tells the compiler how to read data while FORMAT tells the compiler how to write data. FORMATs are just visible representations of numbers (or characters).

Changing Date Formats

By default, ddmmyy. reads seven characters. To specify year with century use ddmmyy10.

Calculating Differences Between Dates

Here today() in SAS is like Sys.Date() in R. We simply subtract dates in SAS (instead of using difftime in R)

data dates_diff;
format dob date9. b date9.;dob=input("7jun1977",date9.);
b=today();
c=dob-b;
run;

proc print data=dates_diff;
Run;

Obs     dob            b             c
1       01APR1977      09DEC2018     15227

Using Intck Option

We can use datepart to extract date and use intck to obtain difference in dates

data new3;
format a1 b1 date9.;
a0='01jan2009:00:00:00'dt;
b0='01jan2012:00:00:00'dt;
a1=datepart(a0);
b1=datepart(b0);
days=intck('day',a1,b1);
run;

proc print data=NEW3;
run;

Obs   a1         b1         a0          b0          days
1     01JAN2009  01JAN2012  1546387200  1640995200  1095

proc contents data=NEW3;
run;

# Variable     Type    Len     Format
3     a0       Num     8      
1     a1       Num     8       DATE9.
4     b0       Num     8       
2     b1       Num     8       DATE9.
5     days     Num     8

4.2.2 Handling Dates in R

Having your dates in the proper format allows R to know that they are dates, and what calculations it should perform on them.

The builtin as.Date function handles dates (without times). The as.Date function allows a variety of input formats through the format = argument.

The default format is a four‐digit year, followed by a month, then a day, separated by either dashes or slashes.

>as.Date("2018-06-16")
[1]"2018-06-16"
> as.Date('16 June 2018',format="%d %B %Y")
#%d consists of the day, %B consists of full month name and %Y contains the year with century.
[1] "2018-06-16"

Consider another example for the usage of format option in as.Date function.

tz option is used to specify a time zone.

CONVERTING DATE TO NUMERIC VALUE

R stores dates using 1 January 1970 as the origin. When R looks at dates as integers it calculates the days passed since 1 January 1970.

CONVERTING DATE TO CHARACTER VALUE

USING Sys.Date FUNCTION

To get the current date, the Sys.Date function will return a Date object.

Using Posixct Function

Date and time are stored using POSIXct function but it can parse only the format of YYYY‐MM‐DD HH:MM:SS. strptime is used for different formats.

The formatting and order variations of the date pieces is what strptime deals with so as.POSIXct can recognize the date.

> Sys.Date()
[1] "2018-12-09"

> Sys.time()
[1] "2018-12-09 12:33:20 IST"

> as.POSIXct(Sys.Date())
[1] "2018-12-09 05:30:00 IST"

> as.POSIXct(strptime("09/12/2018 12:33", format = "%d/%m/%Y %H:%M"), tz ="Asia/Calcutta")

[1] "2018-12-09 12:33:00 IST"

Giving Time Differences Between Two Dates using difftime

> test1<-as.Date("2018-01-01", tz="UTC")
> test2<-strptime("2016-01-25", format="%Y-%m-%d", tz="UTC")

> result=difftime(as.POSIXct(test2), as.POSIXct(test1,tz="UTC"),units="days")

> result
Time difference of -707 days

Lubridate Package

R is greatly simplified and augmented by the lubridate package. It has many functions but primarily the format either says ‘dmy’, ‘mdy’, “ymd” which can be easily used in R.

Giving Time Differences using Lubridate Package

> adob="21 June 1977"
> adob2=dmy(adob)
> adob2
[1] "1977-06-21"

> mydob="15 June 1981"
> mydob2=dmy(mydob)
> mydob2
[1] "1981-06-15"

> difftime(adob2,mydob2,units="days")
Time difference of -1455 days

4.3 Handling Strings Data

A vast amount data is in the form of text particularly emails, documents and the Internet. The ability to manipulate string data types is critical to a data scientist.

A “string” is a collection of characters that make up one element of a vector. You can tell a string because it will be mostly be enclosed in (double) quotation marks.

4.3.1 Handling Strings Data in SAS

Assigning String Values to Variables

data string;
LENGTH string1 $ 6 String2 $ 5;
/*String variables of length 6 and 5 */
String1 = 'hi';
String2 = 'user';
 run;
proc print data=string;
Run;

Obs     string1     String2
1       hi          user

Data names;
INPUT name $30.;
DATALINES; /* here we have used to separate the first name, middle name ,last name */
a b c
Ajay singh ohri
lora Marie
;
run;

proc print data=names;
Run;

Obs    name
1      a b c
2      Ajay singh ohri
3      lora Marie

Using SUBSTRN option

data substring;
length string string1 string2 $12;
string="Hello World";
string1 = substrn(String,3,4) ; /*Extract from position 3 to 7 (3+4) */
string2 = substrn(String,4) ;/*Extract from position 4 onwards */
run;

proc print data = substring ;
run;


Obs   string        string1   string21     Hello World     llo     lo World

Using Trimn Function

data string;
LENGTH str1 $ 12 ;
 str1='Hello World';
   length1 = lengthc(Str1);
   length_trim2 = lengthc(TRIMN(str1));
 run;

proc print data=string;
run;

Obs     str1            length1     length_trim2
1       Hello World     12          11

CONCATENATION OF STRING

DATA abc;
INPUT abc1 $char3. abc2 $char3. ;
DATALINES;
ab cd
;
run;

DATA result;
SET abc;
concate=abc1||abc2;
run;

proc print data=result;
Run;

Obs     abc1     abc2     concate
1       ab       cd       ab cd

data string1;
LENGTH string1 $ 6 String2 $ 5;
/*String variables of length 6 and 5 */
String1 = 'hi';
String2 = 'user';
combined_string = String1||String2 ;
run;

proc print data=string1;
Run;

Obs     string1      String2      combined_string
1       hi           user         hi user

TO CONVERT CHARACTER TO NUMERIC

data new;
char = '1234567';
numeric = input(char, 8.);
Run;

proc contents data=new;
run;


#     Variable     Type     Len
1     char         Char     7
2     numeric      Num      8

proc print data=new;
Run;

Obs     char        numeric
1       1234567     1234567

Replace One Value of String with Another‐ Here, translate function replaces a with &

data a;
input x $40.;
x1=translate(x,'a','&');
cards;
My&Name&IS&akansha
Hello&World
;
Run;

proc print data=a;
Run;

Obs   x     x1
1     My&Name&IS&akansha     MyaNameaISaakansha
2     Hello&World   HelloaWorld

Eliminating Whitespace using compress

data a;
input x $40.;
cards;
"My&Name &IS &akansha"
"Hello & World"
;
Run;

data a2;
set a;
x1=compress(x,' ');
run;

proc print data=a2;
run;

Obs   x                           x1
1    "My&Name &IS &akansha"    "My&Name&IS&akansha"
2    "Hello & World"           "Hello&World"

4.3.2 Handling Strings Data in R

In R, a piece of text is represented as a sequence of characters (letters, numbers, and symbols). The data type R provided for storing sequences of characters is character. Formally, the mode of an object that holds character strings in R is “character”.

You express character strings by surrounding text within double quotes or single quotes.

ASSIGNING STRING VALUE TO AN OBJECT:

SPECIAL CHARACTERS IN STRINGS

PRINT FORMATTED STRING

COUNTING CHARACTERS IN STRING

VECTOR STRINGS

USING EXTRACTION AND ASSIGNMENT OPERATORS

CONVERTING NUMERIC TO STRINGS

PARSING STRINGS TOGETHER

BREAKING A STRING AT A DELIMITER

USING DescTools, trimws IN R ELIMINATES WHITE SPACE

STRING TO NUMERIC

Note R has another data type called factor which is used for categorical variables. They may look like string data but usually have a few levels and are identified as factor variables in R. In SAS they would be character and analyzed using Proc Freq.

4.4 Quiz Questions

How do you create a vector in R?
Give the function used to name the vector elements.
Name the functions used to convert numeric data type to character data type in R.
How can you simplify date handling in R?
Give the name of function to calculate the difference between two dates in R?
Give the name of function to calculate the difference between two dates in SAS?
Give three different types of date format and their syntax in SAS.
Calculate the characters in string “R and SAS” in R.
Concatenate the strings “Hello” and “World” in SAS and R.
What is the command used to eliminate white space in R.
11 11 Name a package in R that handles strings data.

Quiz Answers

Vectors are created using c() command.
names(vector_name) command is used to name the elements of a vector.
In R: as.character.
Date handling can be simplified using lubridate function.
In R: difftime
In SAS: intck
17‐07‐18 ddmmyy8., 07–17‐18 mmddyy8., 17jul2018 date9.
>nchar(“R and SAS”)

In R: paste(“Hello”, “World”, sep=” “) In SAS:

Using DescTools package, trimws command eliminates whitespace in R. We can also use gsub ( gsub(“ “,,dataframe$variable) )
stringr package

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.