[R] Replicate an SAS/SQL request

[Guibert TCHINDE]

I am very confuse,
I send with a wrong subject. 
All my Apologizes

-----
________________________________________
De : Guibert TCHINDE
Date d'envoi : jeudi 1 août 2013 18:51
À : r-help at r-project.org
Cc : R help
Objet : RE : [R] Conversion of matrix in r to integer

Dear List,

I am trying to replicate an SQL request run wih SAS using R
The request is :

PROC SQL;
        CREATE VIEW myview AS
        SELECT a.*,
                   b.semaine
                   b.date_debut
                   b.date_fin
FROM myfirsttable. a
INNER JOIN
                mycalendar
                ON (  a.DEBUT1    <=  b.date_fin  AND a.FIN>=  b.date_debut   AND (a.MIG = 'O' AND a.DATE_MIG <= b. date_fin)
QUIT;

Like we see, ther's not join key between the two datasets and the job run by sas is to : make cartesian product and subsetting according to the conditions


So when i try to replicate this in R. I do this :

> system.time(extract<-merge(myfirsttable,mycalendar))
utilisateur     système      écoulé
    6509.51       47.61     6792.12
> system.time(VueAnalyse<-subset(extracts,
+                                (DEBUT1<=date_fin & FIN>=date_debut) &
+                                ((MIG == 'O' & DATE_MIG <= date_fin))
utilisateur     système      écoulé
     178.59       14.07      211.11

The first datasets contains 40 millions rows and the second just 5.
So the first cartesian product take ~2 hours.

Somebody have any idea to do this in R with less time?

Thanks in advance

Regards,

GT