Date:         Mon, 28 Oct 2002 14:25:58 -0500
Reply-To:     Ed Heaton <EdHeaton@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ed Heaton <EdHeaton@WESTAT.COM>
Subject:      Re: Yes, folks, ANOTHER SQL question!
Comments: To: Talbot Katz <TopKatz@MSN.COM>
Content-Type: text/plain

Katz,

If I understand your need correctly, this might give your desired results.

%let ids = 10 ;
%let keys = 8 ;
%let seed = 183527 ;

/* First create the test data sets. */
Data A( keep= Id Key ) ;
   Do Id=1 to &ids ;
      Dups = ceil( ranUni(&seed) * 10 ) ;
      Do i=1 to Dups ;
         Key = ceil( ranUni(&seed) * &keys ) ;
         Output ;
      End ;
   End ;
Run ;
Data B( keep= Key Level ) ;
   Do Key=1 to &keys ;
      Level = ceil( ranUni(&seed) * 5 ) ;
      Output ;
   End ;
Run ;

/* Here is your SQL code. */
Proc sql ;
   Create table new( keep= Id MaxLevel) as
        select Id , level , max(Level) as MaxLevel
      from A , B
      where ( A.Key eq B.Key )
      group by Id
      having ( calculated MaxLevel eq Level )
   ;
Quit ;

Ed

Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice:  (301) 610-4818                 Fax:  (301) 294-3992
mailto:EdHeaton@westat.com             http://www.westat.com


-----Original Message-----
From: Talbot Katz [mailto:TopKatz@MSN.COM]
Sent: Monday, October 28, 2002 1:18 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Yes, folks, ANOTHER SQL question!


Yeah, it's me again, displaying my usual ignorance.

Here's what I want to do.  Suppose I have two tables, A and B.  Table A
contains two fields, id and key; for each id, there may be several values
of key.  Table B contains two fields, key and level, and each record is
unique; for each key, there is one and only one value of level.  I want to
match tables A and B by key, naturally, but I don't want to keep all the
matches.  I only want to keep the maximum levels for each id.  If I didn't
care about the key value, the following would suffice :

  proc sql ;
    create table hilev as
      select distinct id, max(level)
        from A, B
        where A.key = B.key
        group by id
        order by id ;
  quit ;

but, silly me, I want to keep all the key values which have the maximum
level.  I can do this with one SQL step, followed by a data step :

  proc sql ;
    create table allev as
      select distinct id, key, level
        from A, B
        where A.key = B.key
        group by id, key
        order by id, level desc, key ;
  quit ;

  data hilev ;
    set allev ;
      by id descending level ;
    retain outflag 0 ;
    drop outflag ;
    if first.level then do ;
      if first.id then do ;
        outflag = 1 ;
      end ;
      else do ;
        outflag = 0 ;
      end ;
    end ;
    if outflag then output ;
  run ;

Can this whole thing be done entirely with only one SQL step?

Thanks!

--  TMK  --