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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.