title: Own function in RSQL Lite Engine in R
body: <p>I found this SQL code for SAS and I want to translate it into RSQL Lite.</p>
  
  <hr>
  
  <pre><code>proc sql;
  create table crspcomp as
  select a.*, b.ret, b.date
  from ccm1 as a left join crsp.msf as b
  on a.permno=b.permno
  and intck('month',a.datadate,b.date)
  between 3 and 14;
  quit;
  </code></pre>
  
  <hr>
  
  <p>The first Problem which occurred was R does not provide the intck function, which Returns the difference in months between two dates. I found a similar function (at stackoverflow) which looks like this:</p>
  
  <pre><code>mob&lt;-function (begin, end) {
    begin&lt;-paste(substr(begin,1,6),"01",sep="")
    end&lt;-paste(substr(end,1,6),"01",sep="")
    mob1&lt;-as.period(interval(ymd(begin),ymd(end)))
    mob&lt;-mob1@year*12+mob1@month
    mob
  }
  </code></pre>
  
  <p>I've tested the mob function outside RSQL and it works fine so far. Now I want to put the mob function into the SQL Statement written above. 
  In the SQL Code I want to merge the data on permno and in addition I want to lag the data for 3 months (thats why I use the mob function). </p>
  
  <hr>
  
  <p>The Annual_File looks like this:</p>
  
  <pre><code>GVKEY,datadate,fyear,fyr,bkvlps,permno
  14489,19980131,1997,1,4.0155,11081
  14489,19990131,1998,1,1.8254,11081
  14489,20000131,1999,1,2.0614,11081
  14489,20010131,2000,1,2.1615,11081
  14489,20020131,2001,1,1.804,11081
  </code></pre>
  
  <p>The CRSP file looks like this</p>
  
  <pre><code>permno,date,ret
  11081,20000103,0.1
  11081,20000104,0.2
  </code></pre>
  
  <hr>
  
  <pre><code>install.packages('DBI')
  install.packages('RSQLite')
  
  
  mob&lt;-function (begin, end) {
    begin&lt;-paste(substr(begin,1,6),"01",sep="")
    end&lt;-paste(substr(end,1,6),"01",sep="")
    mob1&lt;-as.period(interval(ymd(begin),ymd(end)))
    mob&lt;-mob1@year*12+mob1@month
    mob
  }
  
  Annual_File &lt;- "C:/Users/XYZ"
  Annual_File  &lt;- paste0(Annual_File ,".csv",sep="")
  
   inputFile &lt;- "C:/Users/XYZ"
   inputFile &lt;- paste0(inputFile.csv",sep="")
  
  con &lt;- dbConnect(RSQLite::SQLite(), dbname='CCM')
  
  dbWriteTable(con, name="CRSP", value=inputFile, row.names=FALSE, header=TRUE, overwrite=TRUE)
  dbWriteTable(con, name="Annual_File", value=Annual_File, row.names=FALSE, header=TRUE, overwrite=TRUE)
  
  
  
   DSQL &lt;- "select a.*, b.ret, b.date 
            from Annual_File as a left join
            CRSP as b
            on a.permno=b.PERMNO
            and mob(a.datadate,b.date)
                  between 3 and 14"
  
  
    yourData &lt;- dbGetQuery(con,DJSQL)
  </code></pre>
  
  <p>Even tough I defined the function - the Error looks as follows.</p>
  
  <pre><code>Error in sqliteSendQuery(con, statement, bind.data) : 
    error in statement: no such function: mob
  </code></pre>