Skip to content

Reference a variable inside a string and another for object assingments

6 messages · Greg Snow, MacQueen, Don, Gabor Grothendieck +1 more

Kenneth Rose
# 
Hi R community

I copied a bit of my R code that gets some data from a database. You
won't be able to run the code, but I am a beginner so you will
probably understand what going on.

I would like to make a variable I can refer to inside the sqlQuery.
Instead of writing the start date and time (ex SP.lokaldatotid >=
'2005-01-01 00:00:00') inside the query I would like to define it in
the beginning of the code, so I don't have to fiddle with a lot of
dates each time I wan't to change it. I would like to do this for a
few of the variables and maybe even make a list/array I can loop
through, so I don't have to write the same code multiple times (for
SYS and DK1).

I have searched for a solution for two days now, but I am not sure
what it's called and are probably writing the wrong queries :-)


Thank you for your help!

Kenneth

My code:

library(xts)
library(RODBC)


#Define channnel (i configured my own SYSTEM-DNS, via ODBC)
ch <- odbcConnect("DI2")

#####################################################################
############################## GET DATA    ##########################
#####################################################################

############################## SYSTEM spot ##########################
# Hent data fra SQL Server
sys <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                           "WHERE (SP.omraade_id= 0 AND
SP.lokaldatotid >= '2005-01-01 00:00:00')"))
#Definer dato og tid kolonne
sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid)

#Make a XTS object
sys_xts <- xts(sys[,-1], order.by=sys[,1])

# Recalculate data from hours to daily, monthly and yearly averages
sys_xts_daily <- apply.daily(sys_xts, FUN=mean)
sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean)
sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean)


############################## DK1 spot #############################
# Hent data fra SQL Server
dk1 <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                           "WHERE (SP.omraade_id= 5 AND
SP.lokaldatotid >= '2005-01-01 00:00:00')"))
#Definer dato og tid kolonne
dk1$lokaldatotid <- as.POSIXct(dk1$lokaldatotid)

#Lav om til xts object
dk1_xts <- xts(dk1[,-1], order.by=dk1[,1])

#Data omregnet fra time ->> daglig, m?ned, ?rlige gennemsnit
dk1_xts_daily <- apply.daily(dk1_xts, FUN=mean)
dk1_xts_monthly <- apply.monthly(dk1_xts, FUN=mean)
dk1_xts_yearly <- apply.yearly(dk1_xts, FUN=mean)
Greg Snow
# 
Perhaps the sprintf function is what you are looking for.  It is one
way to insert information from a variable into a string.  A couple of
other options are paste, paste0, and the gsubfn package, but I think
sprintf will be simplest for what you are asking.
On Thu, Aug 16, 2012 at 1:30 PM, Kenneth Rose <kennethrose82 at gmail.com> wrote:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      MacQueen, Don
    

    

      
      #
    

  


  

      
I sometimes do this sort of thing with "tricks" like this:

  sql <- "select * from mytable where dt >= 'ADATE'"

  dbGetQuery( con, gsub('ADATE', '2012-06-12 23:14', sql) )



Or if mydates is a vector of dates stored as a POSIXt object:

for (id in mydates) {
 dbGetQuery( con,  gsub('ADATE', format(id), sql) )
}


-Don

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Kenneth Rose
    

    

      
      #
    

  


  

      
I tried these two and testquery2 now does the job :) Somehow the str_c
function from the stringr package doesn't work when I run the sqlQuery
function even though the testquery string is the same as testquery2
string. Thanks for the pointers!

fromdate <- c("'2005-01-01 00:00:00'")

testquery <- str_c("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                   "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >=
",fromdate[1],")")

testquery2 <- paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                    "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >="
,fromdate[1],")")

I still can't figure out how to assign an existing xts object to a
name from a list or array. I have data that i want to rename to one of
the names i a character vector.

Example:


names <- c("data2", "data3")

data <- xts(11:10, Sys.Date()+1:10)

# The next part is not real code but just so you get the basic idea:

names[1] <- data
remove(data)

data2 should now be assigned to data.


Thanks for your help with the first part!!

/Kenneth

        
On Thu, Aug 16, 2012 at 10:57 PM, MacQueen, Don <macqueen1 at llnl.gov> wrote:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Gabor Grothendieck
    

    

      
      #
    

  


  

      
On Thu, Aug 16, 2012 at 3:30 PM, Kenneth Rose <kennethrose82 at gmail.com> wrote:

            

      
      
      
    

        
You can use paste as shown in the example in ?sqlGetResults or
fn$ in the gsubfn package can do quasi-perl-like string interpolation.
With fn you just preface any command with fn$ and then its
arguments are subject to string interpolation as explained further
in ?fn and http://gsubfn.googlecode.com. e.g.

library(gsubfn)

id <- 5
date <- '2005-01-01 00:00:00'

dk1 <- fn$sqlQuery (ch, "SELECT  SP.lokaldatotid, SP.pris FROM
	DataIndsamling2.dbo.SpotPriser SP
	WHERE (SP.omraade_id = $id AND
	SP.lokaldatotid >= '$date' )" )

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Kenneth Rose
    

    

      
      #
    

  


  

      
Thanks Gabor!

This is exactly what I was searching for! --- And it works like a charm.


On Fri, Aug 17, 2012 at 1:03 PM, Gabor Grothendieck

        
<ggrothendieck at gmail.com> wrote: