Hi
are there any easy way to keep the last
two observations within a group?
Thanks
thomas
Do you have another variable that controls the sort as well as your group variable?
Easy? No. But at least it's not terribly lengthy:
data want;
total_count=0;
do until (last.group);
set have;
by group;
total_count + 1;
end;
running_count=0;
do until (last.group);
set have;
by group;
running_count + 1;
if running_count >= total_count - 1 then output;
end;
drop running_count total_count;
run;
Good luck.
data have;
input group a $ b c;
datalines;
1 A 23 20
1 B 34 32
1 C 29 12
1 D 24 10
2 E 45 65
2 F 87 25
2 G 89 76
;
data want;
set have;
by group;
if first.group then cnt=1;
else cnt+1;
run;
proc sql;
select * from want
group by group
having cnt>max(cnt)-2
order by group,cnt;
quit;
Consider this PROC SUMMARY.
How about :
data have; input group a $ b c; datalines; 1 A 23 20 1 B 34 32 1 C 29 12 1 D 24 10 2 E 45 65 2 F 87 25 2 G 89 76 ; run; data want(drop=_group); merge have have(keep=group rename=(group=_group) firstobs=3); if group ne _group; run;
Xia Keshan
Nice! Keep it coming, buddy!
Here's another option
data have;
input group a $ b c;
datalines;
1 A 23 20
1 B 34 32
1 C 29 12
1 D 24 10
2 E 45 65
2 F 87 25
2 G 89 76
;
run;
proc sort data=have;
by descending group descending a ;
run;
data want;
set have;
by descending group descending a;
retain icount;
if first.group then icount=0;
if icount<2 then do;
output;
icount++1;
end;
drop icount;
run;
proc sort data=want;
by group a;
run;
Hi,
Problem solved!
Many thanks for all brilliant answers!
/Thomas
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.