Menu
I need to create a new variable "controldates" from a date variable "casedates". This new variable is going to consist of dates that are on the same day of the week as the casedate, within the same month and year as the case date. For example if I have a case date on the 3rd Wednesday of July my control days will be the first 1st Wednesday of July, the second Wednesday of July, and the 4th Wednesday of July. Additionally, I would like to create an indicator variable for each group of dates that are created. I would like to do this using dplyr in r.
Starting data:
Casedate
"01-03-2015"
"08-27-2017"
"10-23-2019"
This is how I would like it to look
Casedate Controldate Index
"01-03-2015" "01-03-2015" 1
"01-03-2015" "01-10-2015" 1
"01-03-2015" "01-17-2015" 1
"01-03-2015" "01-24-2015" 1
"01-03-2015" "01-31-2015" 1
"08-12-2017" "08-05-2017" 2
"08-12-2017" "08-12-2017" 2
"08-12-2017" "08-19-2017" 2
"08-12-2017" "08-26-2017" 2
"10-23-2019" "10-02-2019" 3
"10-23-2019" "10-09-2019" 3
"10-23-2019" "10-16-2019" 3
"10-23-2019" "10-23-2019" 3
"10-23-2019" "10-30-2019" 3
606
To create a Date object from a simple character string in R, you can use the as. Date() function. The character string has to obey a format that can be defined using a set of symbols (the examples correspond to 13 January, 1982): %Y : 4-digit year (1982)
DATA sample; SET sample; date = MDY(mn, days, yr); FORMAT date MMDDYY10.; RUN; Here a new variable date will be created by combining the values in the variables mn , days , and yr using the MDY function. The (optional) MMDDYY10. format tells SAS to display the date values in the form MM/DD/YYYY.
Here is an option with tidyverse. Convert the 'Casedate' to Date class with lubridate, then loop over the elements with map, create a sequence of dates in a list, unnest the list column
library(dplyr)
library(purrr)
library(lubridate)
df1 %>%
mutate(Index = row_number(),
Casedate = mdy(Casedate),
wd = wday(Casedate, label = TRUE),
Controldate = map2(floor_date(Casedate, 'month'), wd, ~ {
x1 <- seq(.x, length.out = 7, by = '1 day')
seq(x1[wday(x1, label = TRUE) == .y],
ceiling_date(.x, 'month'), by = '7 day')})) %>%
unnest(c(Controldate)) %>%
select(Casedate, Controldate, Index)
-output
# A tibble: 14 x 3
# Casedate Controldate Index
# <date> <date> <int>
# 1 2015-01-03 2015-01-03 1
# 2 2015-01-03 2015-01-10 1
# 3 2015-01-03 2015-01-17 1
# 4 2015-01-03 2015-01-24 1
# 5 2015-01-03 2015-01-31 1
# 6 2017-08-27 2017-08-06 2
# 7 2017-08-27 2017-08-13 2
# 8 2017-08-27 2017-08-20 2
# 9 2017-08-27 2017-08-27 2
#10 2019-10-23 2019-10-02 3
#11 2019-10-23 2019-10-09 3
#12 2019-10-23 2019-10-16 3
#13 2019-10-23 2019-10-23 3
#14 2019-10-23 2019-10-30 3
df1 <- structure(list(Casedate = c("01-03-2015", "08-27-2017", "10-23-2019"
)), class = "data.frame", row.names = c(NA, -3L))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
