topic sql query in SAS Procedures

sql query

sunilreddy — Fri, 21 Sep 2012 15:24:29 GMT

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.

Re: sql query

Linlin — Fri, 21 Sep 2012 15:37:34 GMT

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

Re: sql query

shivas — Fri, 21 Sep 2012 15:42:44 GMT

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

Re: sql query

Doc_Duke — Fri, 21 Sep 2012 15:48:48 GMT

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

Re: sql query

Doc_Duke — Fri, 21 Sep 2012 15:55:07 GMT

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

Re: sql query

Howles — Fri, 21 Sep 2012 22:02:08 GMT

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;

Re: sql query

MikeZdeb — Sat, 22 Sep 2012 23:11:34 GMT

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;