Repeat many times: R does not work the same way as SAS, do not expect it to
If I needed to construct an SQL statement in which the name of a field is provided by the value of another variable, I would consider this: key <- 'pop' sql.stmt <- paste("select grade, count(*) as cnt, min(",key,") as min, max(",key,") as max, avg(",key,") as mean, median(",key,") as median, stdev(",key,") as stdev from tab group by grade" ) Then print(sql.stmt, quote=FALSE) to make sure I got what I wanted. Use paste0() instead of paste() if you don't want the extra space characters, even though they don't matter to SQL. And, no, the double [[...]] does not remove double quotes. That's not how it works. tab[['pop']] tab[ , 'pop'] tab$pop are three different ways to reference the pop column in the tab data frame. Of the three, the value 'pop' can be stored in a variable with the first two, but not the third one. Remember, R is not SAS, so you have to use a different technique to construct the desired SQL statement; there is nothing in R that works exactly like SAS macro variables. (R has other advantages). -Don -- Don MacQueen Lawrence Livermore National Laboratory 7000 East Ave., L-627 Livermore, CA 94550 925-423-1062 On 2/3/16, 11:16 AM, "R-help on behalf of Amoy Yang via R-help" <r-help-boun...@r-project.org on behalf of r-help@r-project.org> wrote: >First, MVAR<-c("population) should be the same as "population'". Correct? >You use tab[[MVAR]] to refer to "population" where double [[...]] removes >double quotes "...", which seemingly work for r-code although it is >tedious in comparison direct application in SAS %let MVAR=population. But >it does not work for sqldef in R as shown below. > >> key<-"pop" >> library(sqldf) >> sqldf("select grade, count(*) as cnt, min(tab[[key]]) as min, >+ max(pop) as max, avg(pop) as mean, median(pop) as median, >+ stdev(pop) as stdev from tab group by grade") >Error in sqliteSendQuery(con, statement, bind.data) : > error in statement: near "[[key]": syntax error > > > > > On Wednesday, February 3, 2016 12:40 PM, "ruipbarra...@sapo.pt" ><ruipbarra...@sapo.pt> wrote: > > > Hello, > >You can't use tab$MVAR but you can use tab[[MVAR]] if you do MVAR <- >"population" (no need for c()). > >Hope this helps, > >Rui Barradas > Citando Amoy Yang via R-help <r-help@r-project.org>: >population is the field-name in data-file (say, tab). MVAR<-population >takes data (in the column of population) rather than field-name as done >in SAS: %let MVAR=population; >In the following r-program, for instance, I cannot use ... tab$MVAR...or >simply MVAR itself since MVAR is defined as "population" with double >quotes if using MVAR<-c("population") > > On Wednesday, February 3, 2016 11:54 AM, Duncan Murdoch ><murdoch.dun...@gmail.com> wrote: > > >On 03/02/2016 12:41 PM, Amoy Yang via R-help wrote: > There is a %LET statement in SAS: %let MVAR=population; Thus, MVAR can >be used through entire program. >In R, I tried MAVR<-c("population"). The problem is that MAVR comes with >double quote "...." that I don't need. But MVAR<-c(population) did NOT >work out. Any way that double quote can be removed as done in SAS when >creating macro_var? >Thanks in advance for helps! >R doesn't have a macro language, and you usually don't need one. > >If you are only reading the value of population, then > >MAVR <- population > >is fine. This is sometimes the same as c(population), but in general >it's different: c() will remove some attributes, such as >the dimensions on arrays. > >If you need to modify it in your program, it's likely more complicated. >The normal way to go would be to put your code in a function, and have >it return the modified version. For example, > >population <- doModifications(population) > >where doModifications is a function with a definition like > >doModifications <- function(MAVR) { > # do all your calculations on MAVR > # then return it at the end using > MAVR >} > >Duncan Murdoch > > > > [[alternative HTML version deleted]] > >______________________________________________ >R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide >http://www.R-project.org/posting-guide.htmland provide commented, >minimal, self-contained, reproducible code. > > > > > [[alternative HTML version deleted]] > >______________________________________________ >R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >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. ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.