Date:         Mon, 31 Mar 2003 23:31:23 -0500
Reply-To:     Jonathan Siegel <jmsiegel@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jonathan Siegel <jmsiegel@YAHOO.COM>
Subject:      Re: left join last observation
Comments: To: Jimmy Chan <jimmy.chan@HEC.CA>

You can do it in a single SQL step:

proc sql;
  create table new(drop=sasdate) as
  select a.*, b.price, input(put(date, 8.), yymmdd8.) as sasdate
  from dataset1 as a left join dataset2 as b
  on a.type = b.type
  group by year(a.sasdate)
  having a.date = max(a.sasdate)
  ;
quit;

If you're willing to read DATE in as a SAS date rather than a regular
number, you could avoid the extra date conversion in the PROC SQL step and
get:

proc sql;
  create table new as
  select a.*, b.price
  from dataset1 as a left join dataset2 as b
  on a.type = b.type
  group by year(a.date)
  having a.date = max(a.date)
  ;
quit;

Hope this helps,

Jonathan Siegel


On Mon, 31 Mar 2003 10:40:02 -0500, tin-shun-jimmy chan <jimmy.chan@HEC.CA>
wrote:

>Hi all,
>
>I want to know how can I  LEFT JOIN the last observation of each year
>in one dataset to another dataset. The situation is kind of...
>
>Dataset 1
>
>Type quantity   date
> a      30     20000801
> a      45     20000806
> b      50     20000921
> b      52     20000922
> b      16     20000930
>
>Dataset 2
>
>Type price
> a    15
> b    16
>
>The result that I want to obtain is
>
>Type price quantity  date
> a    15     45     20000806
> b    16     16     20000930
>
>I just remember that I need to use some kind of LAST..., so could you
>please tell me how to do this ? Thanks a lot.
>
>Jim