[R] Reference a variable inside a string and another for object assingments

[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:
> On Thu, Aug 16, 2012 at 3:30 PM, Kenneth Rose <kennethrose82 at gmail.com> wrote:
>> 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')"))
>
> 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' )" )
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com