Replicate SAS Array in R

Question

I am trying to recreate this SAS array in R without transposing my data set from wide to long (due to the size of my data). I've looked here to help but can't seem to generalize it.

data esoph_pre1;
    set ESOPH.Pedsf_esophagus &opts;

    *get sequence of esophageal cancer;
    array siterwho {*} SITERWHO1-SITERWHO3;
    array site {*} SITE1-SITE3;
    array yrdx{*} $ YRDX1-YRDX3;

    do i = 1 to 3;
        *set to 0 at b/g of loop;
        esoph_index = .;
        *1 rec for each instance of esophageal of the correct SITERWHO and location (SITE);
        if (SITERWHO{i} in('21010') OR 
        site{i} in('151','152','153','154','155','158','159')) and
        '2004' <= yrdx{i} <= '2013'
        then esoph_index = i;
        if esoph_index ne . then output;
    end;
    drop i;
run;

If you are not familiar with SAS essentially this is looping through each of the 3 columns and determining if the row should be output and saves the index number as esoph_index.

My question is- is there a way to output a new row each time the conditions are satisfied and put the index associated with that iteration (i.e if during the second iteration of a loop the index would say 2)?

Here is my attempt and desired output but would appreciate any input:

#original Data Frame
dx <- data.frame(ID = c(1,2,3),
                 SITERWHO1 = c('21010',NA,'42322'),
                 SITERWHO2 = c('21010','21010','56987'),
                 SITERWHO3 = c(NA,NA,'21010'),
                 SITE1 = c('159', NA,'160'),
                 SITE2 = c('151', '232','160'),
                 SITE3 = c(NA, NA,'154'),
                 YEARRX1 = c('2005','2001','2004'),
                 YEARRX2 = c('2006','2007','2009'),
                 YEARRX3 = c('1998','1989','2004'),
                 stringsAsFactors = FALSE)
#list of codes 
si <- c(as.character(151:159))
#list of years 
yr <- c(as.character(2004:2013))


#list of variables names
siter <- paste0("SITERWHO",1:3)

site <- paste0("SITE", 1:3)

yeardx <- paste0("YEARRX",1:3)

#put list of variables together 
df <- as.data.frame(t(data.frame(siter = siter, site = site, yeardx = yeardx, 
              stringsAsFactors = FALSE)),stringsAsFactors = FALSE)

#conditions work one at a time but need to get index on df
tcond <- dx[(dx[df$V1][1] == '21010'|
      dx[df$V1][2] == si)  &
      dx[df$V1][3] == '2005',]

#can't seem to get the loop to work
lscond <- lapply(df, function(x){
  dx[(dx[df[['x']]][1] == '21010'
      |dx[df[['x']]][2] %in% si ) &
       dx[df[['x']]][3] == yr, ] 
})

    #desired output
desired <-         data.frame(ID = c(1,1,2,3),
                   SITERWHO1 = c('21010','21010',NA,'42322'),
                   SITERWHO2 = c('21010','21010', '21010','56987'),
                   SITERWHO3 = c(NA,NA,NA, '21010'),
                   SITE1 = c('159', '159',NA,'160'),
                   SITE2 = c('151', '151', '232','160'),
                   SITE3 = c(NA, NA,NA, '154'),
                   YEARRX1 = c('2005','2005','2001','2004'),
                   YEARRX2 = c('2006','2006', '2007','2009'),
                   YEARRX3 = c('1998','1998','1989','2004'),
                   Index = c(1,2,2,3),
                   stringsAsFactors = FALSE)

I assume you mean df_vars when you refer to df in your code? — Wimpel

IceCreamToucan IceCreamToucan · Accepted Answer · 2018-09-11T18:59:00

library(purrr)
library(data.table) # just for %between% function

vars <- c('SITERWHO', 'SITE', 'YEARRX')

map(1:3, ~pmap_lgl(dx[paste0(vars, .x)], ~ 
       (..1 == '21010' 
        | ..2 %in% c('151','152','153','154','155','158','159')
       ) & ..3 %between% c('2004', '2013'))) %>% 
  transpose %>% 
  map(which) %>% 
  imap_dfr(~dx[rep(.y, length(.x)),] %>% mutate(Index = .x))


#   ID SITERWHO1 SITERWHO2 SITERWHO3 SITE1 SITE2 SITE3 YEARRX1 YEARRX2 YEARRX3 Index
# 1  1     21010     21010      <NA>   159   151  <NA>    2005    2006    1998     1
# 2  1     21010     21010      <NA>   159   151  <NA>    2005    2006    1998     2
# 3  2      <NA>     21010      <NA>  <NA>   232  <NA>    2001    2007    1989     2
# 4  3     42322     56987     21010   160   160   154    2004    2009    2004     3

Explanation:

Here we check the conditions on the columns ending in 1, for each row.

i <- 1
pmap_lgl(dx[paste0(vars, i)], ~ 
         (..1 == '21010' 
          | ..2 %in% c('151','152','153','154','155','158','159')
         ) & ..3 %between% c('2004', '2013'))
# [1]  TRUE FALSE FALSE

Then we need to do that for those ending in 2 and 3 also, so map is used.

map(1:3, ~pmap_lgl(dx[paste0(vars, .x)], ~ 
         (..1 == '21010' 
          | ..2 %in% c('151','152','153','154','155','158','159')
         ) & ..3 %between% c('2004', '2013')))
# [[1]]
# [1]  TRUE FALSE FALSE
# 
# [[2]]
# [1]  TRUE  TRUE FALSE
# 
# [[3]]
# [1] FALSE FALSE  TRUE

You can see that for the first row the 1-ending columns and 2-ending columns match (1 = TRUE, 2 = TRUE, 3 = FALSE). But the output isn't really grouped that way, it needs to be transposed.

[[1]]
[1]  TRUE  TRUE FALSE

[[2]]
[1] FALSE  TRUE FALSE

[[3]]
[1] FALSE FALSE  TRUE

Then we need to map which over this to get the indices which are TRUE

[[1]]
[1] 1 2

[[2]]
[1] 2

[[3]]
[1] 3

Last we need to select the corresponding row from the data frame, multiple times if necessary (hence rep), and add the new variable (mutate)

imap_dfr(~dx[rep(.y, length(.x)),] %>% mutate(Index = .x))

Replicate SAS Array in R

1 Answers