Help using Base SAS procedures

sql query

Reply
Frequent Contributor
Posts: 115

sql query

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.

Super Contributor
Posts: 1,636

Re: sql query

Posted in reply to sunilreddy

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

Super Contributor
Posts: 349

Re: sql query

Posted in reply to sunilreddy

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

Trusted Advisor
Posts: 2,115

Re: sql query

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

Trusted Advisor
Posts: 2,115

Re: sql query

Posted in reply to sunilreddy

"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;

Regular Contributor
Posts: 184

Re: sql query

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;

Valued Guide
Posts: 765

Re: sql query

Posted in reply to sunilreddy

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

proc summary data=rydata nway;

class id;

output out=lastid (drop=_Smiley Happy idgroup(max(num) out(num name)=);

run;

proc sort data=lastid;

by num;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 278 views
  • 0 likes
  • 6 in conversation