Date:         Tue, 9 Aug 2005 12:21:08 -0700
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: PROC SQL question
Comments: To: Sami <sbindra@GMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

Hi,

See recent posts in SAS-L Archives for differences
between _N_ and monotonic().  With a sequence number
a HAVING clause can match a first.[min()] and last.
[max()] if you have all circumstances of data read in
and sequencing number applied just right.

e.g.

proc sql;
    create table result(drop=seqno) as
    select
             *,
             monotonic() as seqno
    from
             abc
    group by
             var1,
             var2
    having
             max(seqno) eq seqno
    order by
             var1,
             var2
    ;
quit;





Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing








-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sami
Sent: Tuesday, August 09, 2005 12:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC SQL question


Hi All:

I have two basic SAS SQL questions:

1) Do we have any automatic SAS generated variable (as we have _N_) that
can be used in PROC SQL SELECT statement?

2) Do we have alternate way i.e by using PROC SQL to get the values for
FIRST.variable name and LAST.variable name. For Example:

Orignal data:
data abc;
  input var1 $ var2 $ var3 var4 $;
datalines;
1 a 30 11
1 a 10 22
1 a 20 33
1 b 25 44
1 b 30 55
1 b 35 66
2 c 40 77
2 c 45 88
2 c 50 99
2 d 55 111
2 d 60 222
2 d 65 333
3 e 70 444
;
run;

Expected Output:

Obs    var1    var2    var3    var4    vara    varb
1      1       a       20      33       20     33
2      1       b       35      66       35     66
3      2       c       50      99       50     99
4      2       d       65      333      65     333
5      3       e       70      444      70     444


Thanks,
Sami.