R help - Aug 2012 - Conditional merging in R & if then statement

1)I am wandering how the following SQL statement can be written in R language
w/o using sqldf:
create table  detail2 as
select a.*
from   detail a,
       pdetail b
where a.TDATE=b.TDATE
and    (a.STIM >= b.STIM and a.STIM <=b.MAXTIM)

2) when try if then in R it only applies to the 1st row & not to whole
dataset like in SAS. How do you get round that?
in SAS:

data summary;
  set all1;
  if entry='a:prop' then pctexec=stkful/stocks*100;
run;



Thanks in advance for your help.



--
View this message in context:
http://r.789695.n4.nabble.com/Conditional-merging-in-R-if-then-statement-tp4641936.html
Sent from the R help mailing list archive at Nabble.com.

1) just because you don't use sqldf doesn't make it better. merge only
does the join, not the conditions, so you have to apply them after merge.
Untested (you should provide a complete example):

result <- merge ( detail, pdetail, by=TDATE,
suffixes=c(".a",".b") )
result <- subset ( result, STIM.a >= STIM.b and STIM.a <=MAXTIM.b ) 

You can add a select argument to subset to remove the pdetail columns.

2) if is different than ifelse ... read the Introduction to R document, and/or
read the help pages on if and ifelse.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

ramoss <ramine.mossadegh at finra.org> wrote:
>1)I am wandering how the following SQL statement can be written in R
>language
>w/o using sqldf:
>create table  detail2 as
>select a.*
>from   detail a,
>       pdetail b
>where a.TDATE=b.TDATE
>and    (a.STIM >= b.STIM and a.STIM <=b.MAXTIM)
>
>2) when try if then in R it only applies to the 1st row & not to whole
>dataset like in SAS. How do you get round that?
>in SAS:
>
>data summary;
>  set all1;
>  if entry='a:prop' then pctexec=stkful/stocks*100;
>run;
>
>
>
>Thanks in advance for your help.
>
>
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/Conditional-merging-in-R-if-then-statement-tp4641936.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help at r-project.org mailing list
>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.

On Aug 31, 2012, at 10:11 AM, ramoss wrote:
> 1)I am wandering how the following SQL statement can be written in R
language
> w/o using sqldf:
> create table  detail2 as
> select a.*
> from   detail a,
>       pdetail b
> where a.TDATE=b.TDATE
> and    (a.STIM >= b.STIM and a.STIM <=b.MAXTIM)
> 
> 2) when try if then in R
You need to provide code that shows what you did. If you had the output of 

dat <- merge(a, b, by=TDATE)

You could then limit your data depending on the names of the columns and whether
MATIM was a duplicate name in "a" and "b" with something
along the lines of

subset(dat, STIM.x >= STIM.y & STIM.s <=MAXTIM.y)

> it only applies to the 1st row
Yes, that is how "if" is used in R. You need to educate yourself about
R rather than assuming the similar words are necessarily the same function.
read:

?"if"
?ifelse

> & not to whole
> dataset like in SAS. How do you get round that?
> in SAS:
> 
> data summary;
>  set all1;
>  if entry='a:prop' then pctexec=stkful/stocks*100;
> run;
> 
-- 
David Winsemius, MD
Alameda, CA, USA