Date:         Mon, 16 Mar 2009 02:54:16 -0700
Reply-To:     Lonjer <j.leung3@LSE.AC.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lonjer <j.leung3@LSE.AC.UK>
Organization: http://groups.google.com
Subject:      Re: Help with proc sql join
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On 13 Mar, 15:31, Mterje...@RUSSELL.COM ("Terjeson, Mark") wrote:
> Hi Jer,
>
> Here is oneSQLapproach:
>
> data Table1;
>     company='Bedrock Sand and Gravel';
>     date='31may2008'd;
>     country='FlinstonesVille';
>     output;
>     company='Bedrock Sand and Gravel';
>     date='17jun2008'd;
>     country='FlinstonesVille';
>     output;
>     company='Fred and Barneys Pad';
>     date='22aug2008'd;
>     country='NextDoor';
>     output;
>     company='Wilma and Bettys Grill';
>     date='14sep2008'd;
>     country='NextDoor';
>     output;
>     format date date9.;
> run;
>
> data Table2;
>     date='30may2008'd;
>     country='FlinstonesVille';
>     othervar='AAA';
>     output;
>     date='31may2008'd;
>     country='FlinstonesVille';
>     othervar='BBB';
>     output;
>     date='13jun2008'd;
>     country='FlinstonesVille';
>     othervar='CCC';
>     output;
>     date='14jun2008'd;
>     country='FlinstonesVille';
>     othervar='DDD';
>     output;
>     date='18jun2008'd;
>     country='FlinstonesVille';
>     othervar='EEE';
>     output;
>     date='22aug2008'd;
>     country='NextDoor';
>     othervar='FFF';
>     output;
>     date='23aug2008'd;
>     country='NextDoor';
>     othervar='GGG';
>     output;
>     date='11sep2008'd;
>     country='NextDoor';
>     othervar='HHH';
>     output;
>     date='12sep2008'd;
>     country='NextDoor';
>     othervar='III';
>     output;
>     date='19sep2008'd;
>     country='NextDoor';
>     othervar='JJJ';
>     output;
>     format date date9.;
> run;
>
> procsql;
>         create table new as
>         select a.*,
>                b.date as b_date,
>                b.othervar
>         from table1 as a
>         leftjointable2 as b
>           on a.country eq b.country
>          and b.date in(
>                        select max(c.date) as date
>                        from table2 as c
>                        where c.date le a.date
>                          and c.country eq a.country
>                       )
>         ;
> quit;
>
> Hope this is helpful.
>
> Mark Terjeson
> Senior Programmer Analyst
> Investment Management & Research
> Russell Investments
> 253-439-2367
>
> Russell
> Global Leaders in Multi-Manager Investing
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf Of
>
> Lonjer
> Sent: Friday, March 13, 2009 7:25 AM
> To: SA...@LISTSERV.UGA.EDU
> Subject: Help with procsqljoin
>
> Hi,
>
> I have 2 tables
>
> Table1 - company, date, country
> Table2 - date, country, othervariables
>
> Table1 has all possible dates
> Table2 has a subset of all possible dates
>
> I want tojointhe 2 tables on country and date but pick the date from
> Table2 which is less than or equal to Table1's date.
>
> I've done thejoinfor when the dates are qual but not sure how to
> find the closest date match?
>
>  procsql;
>         create table new as
>         select a.*,
>                 b.*
>         from table1 as a
>         leftjoin
>         table2 as b
>         on (a.market = b.market and a.pricedt=b.date)
>
>         ;
>
> quit;
>
> Thanks in advance,
>
> Jer- Hide quoted text -
>
> - Show quoted text -

Thanks for this Mark.

This works but is quite slow. Are there any other ways to do this
other than sql join? Would using a hash table speed things up?

It takes more than 2 minutes to join on over 1000 observations and 82
columns.

Cheers,

Jer