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.
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
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
Be careful with the NOEQUALS option. It can re-arrange the relative order.
"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:
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;
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:
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.