Hello Colleagues, I programmed in SAS for 3 years and would like to switch to a not so costly software product. Hence I started to evaluate R, and my first test look promising. However I have some question: 1. Is it possible to query R files by SQL internally on data frames (not on a database) and how is the syntax (I have the RODBC package installed). I would like to extract year, Quarter, week, from a date column in a data frame (see attachment). After this I want to attach the column to the original data frame. How do I do this in R? Dr .Th.M?hge, PMP? Procurement Technology Center IBM Deutschland GmbH, Hechtsheimer Str.2, D-55131 Mainz Phone: xx49-(0)6131-84-2416 Mobile: xx49-(0)15117457978 e-mail: muehge at de.ibm.com (See attached file: Debug1.csv)
Thorsten Muehge <MUEHGE <at> de.ibm.com> writes:> 1. Is it possible to query R files by SQL internally on data frames (not on > a database) and how is the syntax (I have the RODBC package installed).It is possible to do similar things "conceptually" in R as in SQL---at least the basic SQL queries (I have not tried others). Unlike SQL, R retains the sort order. So far as I know you can not use SQL code to query R data-frames. But you can put SQL code in a .R file and use RODBC or ODBC (I have used this) to send SQL queries to database; you can also get the results from SQL queries back to R as R objects.
For your 1st question, you can write query against the tables in DB using RODBC. Being a SAS programmer, I have to say that reporting function of R is not as good as that of SAS. On 9/13/06, Thorsten Muehge <MUEHGE at de.ibm.com> wrote:> > > Hello Colleagues, > I programmed in SAS for 3 years and would like to switch to a not so costly > software product. > > Hence I started to evaluate R, and my first test look promising. > > However I have some question: > > 1. Is it possible to query R files by SQL internally on data frames (not on > a database) and how is the syntax (I have the RODBC package installed). > > I would like to extract year, Quarter, week, from a date column in a data > frame (see attachment). After this I want to attach the column to the > original data frame. > > How do I do this in R? > > Dr .Th.M?hge, > > PMP(r) > Procurement Technology Center > IBM Deutschland GmbH, Hechtsheimer Str.2, D-55131 Mainz > Phone: xx49-(0)6131-84-2416 > Mobile: xx49-(0)15117457978 > e-mail: muehge at de.ibm.com > (See attached file: Debug1.csv) > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > > >-- WenSui Liu (http://spaces.msn.com/statcompute/blog) Senior Decision Support Analyst Health Policy and Clinical Effectiveness Cincinnati Children Hospital Medical Center
Wensui Liu wrote:> For your 1st question, you can write query against the tables in DB using RODBC. > > Being a SAS programmer, I have to say that reporting function of R is > not as good as that of SAS.I beg to differ. See for example http://biostat.mc.vanderbilt.edu/StatReport Frank Harrell> > > > On 9/13/06, Thorsten Muehge <MUEHGE at de.ibm.com> wrote: >> >> Hello Colleagues, >> I programmed in SAS for 3 years and would like to switch to a not so costly >> software product. >> >> Hence I started to evaluate R, and my first test look promising. >> >> However I have some question: >> >> 1. Is it possible to query R files by SQL internally on data frames (not on >> a database) and how is the syntax (I have the RODBC package installed). >> >> I would like to extract year, Quarter, week, from a date column in a data >> frame (see attachment). After this I want to attach the column to the >> original data frame. >> >> How do I do this in R? >> >> Dr .Th.M?hge, >> >> PMP(r) >> Procurement Technology Center >> IBM Deutschland GmbH, Hechtsheimer Str.2, D-55131 Mainz >> Phone: xx49-(0)6131-84-2416 >> Mobile: xx49-(0)15117457978 >> e-mail: muehge at de.ibm.com >> (See attached file: Debug1.csv) >> >> ______________________________________________ >> R-help at stat.math.ethz.ch mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. >> >> >> > >-- Frank E Harrell Jr Professor and Chair School of Medicine Department of Biostatistics Vanderbilt University
well, Harrell, I understand sweave or R2html could be a solution. but please show me their applications in a large business setting. On the contrary, I can give you many such cases using SAS. On 9/13/06, Frank E Harrell Jr <f.harrell at vanderbilt.edu> wrote:> Wensui Liu wrote: > > For your 1st question, you can write query against the tables in DB using RODBC. > > > > Being a SAS programmer, I have to say that reporting function of R is > > not as good as that of SAS. > > I beg to differ. See for example > http://biostat.mc.vanderbilt.edu/StatReport > > Frank Harrell > > > > > > > > On 9/13/06, Thorsten Muehge <MUEHGE at de.ibm.com> wrote: > >> > >> Hello Colleagues, > >> I programmed in SAS for 3 years and would like to switch to a not so costly > >> software product. > >> > >> Hence I started to evaluate R, and my first test look promising. > >> > >> However I have some question: > >> > >> 1. Is it possible to query R files by SQL internally on data frames (not on > >> a database) and how is the syntax (I have the RODBC package installed). > >> > >> I would like to extract year, Quarter, week, from a date column in a data > >> frame (see attachment). After this I want to attach the column to the > >> original data frame. > >> > >> How do I do this in R? > >> > >> Dr .Th.M?hge, > >> > >> PMP(r) > >> Procurement Technology Center > >> IBM Deutschland GmbH, Hechtsheimer Str.2, D-55131 Mainz > >> Phone: xx49-(0)6131-84-2416 > >> Mobile: xx49-(0)15117457978 > >> e-mail: muehge at de.ibm.com > >> (See attached file: Debug1.csv) > >> > >> ______________________________________________ > >> R-help at stat.math.ethz.ch mailing list > >> https://stat.ethz.ch/mailman/listinfo/r-help > >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > >> and provide commented, minimal, self-contained, reproducible code. > >> > >> > >> > > > > > > > -- > Frank E Harrell Jr Professor and Chair School of Medicine > Department of Biostatistics Vanderbilt University >-- WenSui Liu (http://spaces.msn.com/statcompute/blog) Senior Decision Support Analyst Health Policy and Clinical Effectiveness Cincinnati Children Hospital Medical Center
I don't believe that doing a direct SQL query on a native R object is currently possible, others have pointed out ways to do some of the things you would want SQL for using built-in R commands. If you really want to use SQL you could transfer the data frames you want to use to database tables, then query those and return the result. You may want to look at the RSQLite and SQLiteDF packages that would help with these steps without requiring any database setup outside of R. It probably not be too much work to write a function that would take an SQL query as a string and a list of data frames as arguments, copy the data frames to SQLite tables (SQLiteDF function sql.data.frame does this), then submit the query on those data frames (using RSQLite package) and return the result. Hope this helps, -- Gregory (Greg) L. Snow Ph.D. Statistical Data Center Intermountain Healthcare greg.snow at intermountainmail.org (801) 408-8111 -----Original Message----- From: r-help-bounces at stat.math.ethz.ch [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Thorsten Muehge Sent: Wednesday, September 13, 2006 4:18 AM To: r-help at stat.math.ethz.ch Subject: [R] R-question Hello Colleagues, I programmed in SAS for 3 years and would like to switch to a not so costly software product. Hence I started to evaluate R, and my first test look promising. However I have some question: 1. Is it possible to query R files by SQL internally on data frames (not on a database) and how is the syntax (I have the RODBC package installed). I would like to extract year, Quarter, week, from a date column in a data frame (see attachment). After this I want to attach the column to the original data frame. How do I do this in R? Dr .Th.M?hge, PMP? Procurement Technology Center IBM Deutschland GmbH, Hechtsheimer Str.2, D-55131 Mainz Phone: xx49-(0)6131-84-2416 Mobile: xx49-(0)15117457978 e-mail: muehge at de.ibm.com (See attached file: Debug1.csv)
Un texte encapsul? et encod? dans un jeu de caract?res inconnu a ?t? nettoy?... Nom : non disponible Url : https://stat.ethz.ch/pipermail/r-help/attachments/20060913/6227aa1b/attachment.pl
Thorsten Muehge wrote:> > Hello Colleagues, > I programmed in SAS for 3 years and would like to switch to a not so costly > software product. > > Hence I started to evaluate R, and my first test look promising. > > However I have some question: > > 1. Is it possible to query R files by SQL internally on data frames (not on > a database) and how is the syntax (I have the RODBC package installed). > > I would like to extract year, Quarter, week, from a date column in a data > frame (see attachment). After this I want to attach the column to the > original data frame. > > How do I do this in R?Hi Thorsten, You may just want to convert the dates as follows, adding them to the data frame on the fly: test.df<-data.frame(dates=as.POSIXlt(c("2004-05-13","2005-07-23","2006-09-13")), nums=rnorm(3)) test.df$years<-format(test.df$dates,"%Y") test.df$quarters<-floor((as.numeric(format(test.df$dates,"%m"))-1)/3)+1 test.df$week<-format(test.df$dates,"%U") test.df Jim
justin bem <justin_bem <at> yahoo.fr> writes:> > Mr Harrell, > > After reading discussion about R output and SAS output , I will like to userreport package. I a windows XP> user > > SincerlySee: http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/Rreport Anupam.
Anupam Tyagi wrote:> justin bem <justin_bem <at> yahoo.fr> writes: > >> Mr Harrell, >> >> After reading discussion about R output and SAS output , I will like to use > rreport package. I a windows XP >> user >> >> Sincerly > > See: > > http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/Rreport > > Anupam. >rreport has not been put in a package but all the source code is available on a per-function basis in our online cvs repository. What is really lacking is documentation but there is a detailed example on the above web site. -- Frank E Harrell Jr Professor and Chair School of Medicine Department of Biostatistics Vanderbilt University