[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: left join last observation
From:       David G Nasser <david_nasser () EARTHLINK ! NET>
Date:       2003-03-31 16:45:54
[Download RAW message or body]

This isn't difficult to do with proc sql.

One tested approach:

data d1;
   input Type $ quantity   date :yymmdd8.;
   format date date9.;
  datalines;
         a      30     20000801
         a      45     20000806
         b      50     20000921
         b      52     20000922
         b      16     20000930
;
  run;

data d2;
   input Type $ price;
  datalines;
         a    15
         b    16
;
  run;

proc sql;
   select d1.*, d2.price
   from d2 left join
      (select *
       from d1
       group by type
       having date = max(date)) d1
   on d1.type = d2.type
 ;
   quit;

Hope it is helpful.

David

*-----------------------------------------------------------------*
*- David Nasser                         d_nasser@alum.wustl.edu  -*
*-----------------------------------------------------------------*


On Mon, 31 Mar 2003 10:40:02
 tin-shun-jimmy chan 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
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003

[prev in list] [next in list] [prev in thread] [next in thread]