Data transformation "proc transpose" equivqlent in R

Question

Hi everyone I am new here I have the follwing dataset

member_id<-c(603,603,603)
fill_date<-c("02/17/2005","06/13/2005","08/11/2005")
drug<-rep("a",3)
days_supply<-rep(30,3)
dataset<-data.frame(member_id,fill_date,drug,days_supply)

I want to transform the data as the follwing: Transformed data

In sas I use this code:

    proc sort data=claims;
       by member_id fill_dt;
       run;
    proc transpose data=claims out=fill_dates (drop=_name_) prefix=fill_dt;
    by member_id;
    var fill_dt;
    run;

    proc transpose data = claims out=days_supply (drop=_name_) prefix = days_supply;
    by member_id;
    var days_supply;
    run;

    data both;
    merge fill_dates days_supply;
    by member_id;
    format start_dt end_dt mmddyy10.;
    start_dt=fill_dt1;
    end_dt=fill_dt1+179;
    run;

I was wondering if you could help with th equivalent code in R

Thanks

When asking for help, you should include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Pictures of data aren't helpful because we'd have to re-type them to use them. — MrFlick, Feb 13 '18 at 19:30
Obs member_id fill_dt drug days_supply 946 603 02/17/2005 a 30 947 603 06/13/2005 a 30 948 603 08/11/2005 a 30 — Houssem Missaoui, Feb 13 '18 at 19:32
Those comments are fairly unreadable. You should edit your question to add those lines with proper code formatting. — AfroThundr, Feb 13 '18 at 19:38

score 0 · Answer 1 · answered Feb 13 '18 at 19:51

This isn't an immediate answer, but Hadley has provided these examples in tidyr github issues. https://github.com/tidyverse/tidyr/issues/149

His links to SO questions: http://stackoverflow.com/questions/24929954; http://stackoverflow.com/questions/27247078

score 0 · Accepted Answer · answered Feb 22 '18 at 12:20

This may get you started.

# in case you don't have those packages installed
install.packages("reshape2")
install.packages("tidyverse")

library(reshape2)
library(tidyverse)


member_id<-c(603,603,603)
fill_dt<- c("2005-02-17", "2005-06-13", "2005-08-11")
days_supply<-rep(30,3)
dataset<-data.frame(member_id,fill_dt,days_supply)




dataset_melt <- melt( data =dataset, id.vars = "member_id" )
dataset_melt <- dataset_melt %>% group_by(variable) %>% mutate( variable_n = paste0( variable, row_number() ))

dataset_cast <- data.table::dcast( data = dataset_melt, formula = member_id ~  variable_n, value.var =c("value")  )
dataset_cast <- dataset_cast %>% mutate( start_dt = as.Date(fill_dt1), 
                                         end_dt   = start_dt + 179 )

dataset_cast

To get better help I suggest creating a minimally reproducible example of what you are doing in SAS. This means SAS code which creates the data in SAS, and creates the output that you want. You data is not minimal because you do not use the "drug" variable.

Data transformation "proc transpose" equivqlent in R

2 Answers2