sql query

[sunilreddy]

rydata x1;
input num id $ name $;

datalines4;
1 id1 name1
2 id2 name2
3 id1 ee
;;;;
run;

Hi,

I want to extract the last record from dataset in group by column id. in this case, i want output in below sequence

2 id2 name2

3 id1 ee

could you please help any one in this.

[Linlin]

try:

data rydata ;
input num id $ name $;
datalines4;
1 id1 name1
2 id2 name2
3 id1 ee
;;;;

proc sort data=rydata;
  by id num;
run;

data want;
  set rydata;
   by id ;
   if last.id;
run;
proc sort ;
by num;
proc  print;run;

                                 Obs    num    id     name

                                     1      2     id2    name2
                                     2      3     id1    ee

Message was edited by: Linlin

[shivas]

Hi sunil,

Try this..

data x1;

input num id $ name $;

datalines4;

1 id1 name1

2 id2 name2

3 id1 ee

;;;;

run;

proc sort data=x1 nodupkey noequals;by id ;run;

Thanks,

Shiva

[Doc_Duke]

Be careful with the NOEQUALS option.  It can re-arrange the relative order.

[Doc_Duke]

"last" is not a construct that is used by SQL (it is not part of the ANSI standard).  Therefore, you have to reframe your question to something that you can answer.  For instance, if num is a monotonic sequence number, you could do it in two steps:

• look for the maximum num in a group
• order the output by num.

Something like

PROC SQL;

CREATE TABLE x2 AS

SELECT *

FROM x1

GROUP BY id

HAVING num=max(num)

;

SELECT *

FROM x2

ORDER BY num;

QUIT;

RUN;

[Howles]

I would combine by putting the ORDER BY clause in the first statement.

Doc@Duke wrote:

"last" is not a construct that is used by SQL (it is not part of the ANSI standard).  Therefore, you have to reframe your question to something that you can answer.  For instance, if num is a monotonic sequence number, you could do it in two steps:

look for the maximum num in a group
order the output by num.


Something like
PROC SQL;

CREATE TABLE x2 AS
SELECT *
FROM x1
GROUP BY id
HAVING num=max(num)
;

SELECT *
FROM x2
ORDER BY num;

QUIT;
RUN;

[MikeZdeb]

hi ... using Linlin's data ...

proc summary data=rydata nway;

class id;

output out=lastid (drop=_:) idgroup(max(num) out(num name)=);

run;

proc sort data=lastid;

by num;

run;