Hello all
I have been working on my thesis using R. I am a newbie to R and met a problem
that bothered me for a while due to my lack of acquaintance of R.
I am using R to query from SQL. I got a list of crsp_fundno of G-style mutual
funds which is still alive. I use the following codes and got what I want:
library(RODBC)
channel<-odbcConnect("CRSPFUND")
g.crspfundno<-sqlQuery(channel,"select crsp_fundno from Fund_style where
wbrger_obj_cd = 'G'order by crsp_fundno")
g.crspfundno (got crsp_fundno of G-style fund from Fund_style table)
y.crspfundno<-sqlQuery(channel,"select crsp_fundno from Fund_hdr where
dead_flag
= 'N'and end_dt=20091231 order by crsp_fundno")
y.crspfundno (got crsp_fundno of still alive fund from Fund_hdr table)
g$key<-paste(g.crspfundno$crsp_fundno)
y$key<-paste(y.crspfundno$crsp_fundno)
v.fundno<-intersect(g$key,y$key) (using intersect to get crsp_fundno of
G-style
mutual funds which is still alive.)
v.fundno
What i need to do next is using the v.fundno I got to query from another table
"Monthly_return" to get the mret coresponding to every v.fundno.
I have only a basic idea of the code:
for (i in 1:length(v.fundno)){
gmret<-sqlQuery(channel,"select mret from Monthly_returns where
crsp_fundno toString(v.fundno[i])")
}
gmret
R gave me an error with undefined function "toString"
I know there should be some details added in to get it right,like define an
object or how to convert number to string..
I would be appreciated if anyone gives me any clue about it.
Muting
Muting Zhang wrote:> > Hello all > > I have been working on my thesis using R. I am a newbie to R and met a > problem > that bothered me for a while due to my lack of acquaintance of R. > > I am using R to query from SQL. I got a list of crsp_fundno of G-style > mutual > funds which is still alive. I use the following codes and got what I want: > > library(RODBC) > channel<-odbcConnect("CRSPFUND") > g.crspfundno<-sqlQuery(channel,"select crsp_fundno from Fund_style where > wbrger_obj_cd = 'G'order by crsp_fundno") > g.crspfundno (got crsp_fundno of G-style fund from Fund_style table) > y.crspfundno<-sqlQuery(channel,"select crsp_fundno from Fund_hdr where > dead_flag > = 'N'and end_dt=20091231 order by crsp_fundno") > y.crspfundno (got crsp_fundno of still alive fund from Fund_hdr table) > g$key<-paste(g.crspfundno$crsp_fundno) > y$key<-paste(y.crspfundno$crsp_fundno) > v.fundno<-intersect(g$key,y$key) (using intersect to get crsp_fundno of > G-style > mutual funds which is still alive.) > v.fundno > > What i need to do next is using the v.fundno I got to query from another > table > "Monthly_return" to get the mret coresponding to every v.fundno. > I have only a basic idea of the code: > for (i in 1:length(v.fundno)){ > gmret<-sqlQuery(channel,"select mret from Monthly_returns where > crsp_fundno > toString(v.fundno[i])") > } > gmret > R gave me an error with undefined function "toString" > I know there should be some details added in to get it right,like define > an > object or how to convert number to string.. > > I would be appreciated if anyone gives me any clue about it. > > Muting > >I'm going to take a wild guess and say that the following might work: gmret<-sqlQuery(channel, paste( "select mret from Monthly_returns where crsp_fundno =", v.fundno[i] ) ) If you could post example data, say the results of: head( v.fundno ) I could provide more than a wild guess. Hope it helps! -Charlie ----- Charlie Sharpsteen Undergraduate-- Environmental Resources Engineering Humboldt State University -- View this message in context: http://n4.nabble.com/string-problems-in-R-tp1685419p1685837.html Sent from the R help mailing list archive at Nabble.com.
Hi Charlie Thank you for your advice, but it makes my R dead... My head(v.fundno) is> head(v.fundno)[1] "2899" "2903" "2960" "3094" "3095" "3211" I tried to plug in the specific value like 2890 and 2960 : gmret.2899<-sqlQuery(channel,"select caldt, mret from Monthly_returns where crsp_fundno = 2899 and caldt > 19700630 order by caldt") gmret.2899 gmret.2903<-sqlQuery(channel,"select mret from Monthly_returns where crsp_fundno = 2903 and caldt > 19700630 order by caldt") gmret.2903 test<-cbind(gmret.2899,gmret.2903) test I got what I need:> head(test)caldt mret mret.1 1 19700731 0.07165605 0.015037594 2 19700831 0.02971768 -0.048148148 3 19700930 0.06060606 0.221789883 4 19701030 -0.02993197 -0.103503185 5 19701130 0.01317504 0.008880995 6 19701231 0.06285714 0.045774648 but there's 310 data set I need, I cant do them by hand like this, I need the loop. could u please help me out? Thank you very much Muting -- View this message in context: http://n4.nabble.com/string-problems-in-R-tp1685419p1692612.html Sent from the R help mailing list archive at Nabble.com.
Hi Charlie:
I tried
gmret<-sqlQuery(channel,
paste( "select mret from Monthly_returns where crsp_fundno =",
v.fundno[1] )
)
It works well.. I think you got my problem solved,R just need time to run
the loop, not dead..
Thank you very much
Muting
--
View this message in context:
http://n4.nabble.com/string-problems-in-R-tp1685419p1692657.html
Sent from the R help mailing list archive at Nabble.com.